Pass through Query to Oracle DB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How does one do Pass-through SQL in ADO.net
In particular, passing hinted SQL to Oracle D
Thanks in advance
 
GeoffD

All queries are pass through -- just enclose the hints in the commented hint
after the Select keyword.

Try something like this:

SELECT /*+ ALL_ROWS HASH(A) */ A.FNAME,A.LNAME,B.ADDRESS
FROM CustomerMaster A, Demographics B
WHERE A.EmpID=B.Locator


regards
roy fine
 
Thanks, Roy

I've already specified a hinted SQL to Oracle DB
eg.
---------------
SELECT/*+ INDEX(o idx_order_datetime) +
c.customerNo, c.CustomerName, o.orderNo, o.OrderDateTim
FROM Customer C, Orders
WHERE C.CustomerNo=o.CustomerN
AND o.OrderDateTime between TRUNC(SYSDATE) and SYSDAT
 
GeoffD

One thing to consider - try turning on sql tracing - prior to executing the
SQL comand in question, execute the following comand:
ALTER SESSION SET SQL_TRACE=TRUE
run the SQL command in question, then look in the trace file (you can grep
the BACKGROUND_DUMP_DEST directory to find the file) - you will probably
want to find the trace path name then run TKPROF to get something more
meaningful.

Do this exercise once without the hints, and once with the hints, and look
at the two execution plans -- it will show whether or not the hint was
applied (i.e. different execution plans) - and in fact, you can see the
actual SQL statement that was executed (and whether or not the layers in the
provider/protocol stack did indeed add/take away from the original SQL
statement)..

If you have a PLAN table defined, you can use this SET AUTOTRACE ON and
SQL/Plus will show the execution plan and the execution statistics. If the
plan differs at all between the SQL/Plus and the ADO.Net execution, then we
must investigate further.

It is a bit of a pain, but we achieve 100% isolation by putting the whole
lot in a view - something like this:
CREATE OR REPLACE VIEW V_GEOFFD_DATA AS
SELECT/*+ INDEX(o idx_order_datetime) +/
c.customerNo, c.CustomerName, o.orderNo, o.OrderDateTime
FROM Customer C, Orders o
WHERE C.CustomerNo=o.CustomerNo
AND o.OrderDateTime between TRUNC(SYSDATE) and SYSDATE
Click to expand...

My apologies if all of this is a bit pedantic - if you are clever enough to
be writing optimizer hints, you're well beyond sql trace, explain plans, and
creating views..... :)

I hope this helps...

regards
roy fine

GeoffD said:
Thanks, Roy.

I've already specified a hinted SQL to Oracle DB.
eg.
----------------
SELECT/*+ INDEX(o idx_order_datetime) +/
c.customerNo, c.CustomerName, o.orderNo, o.OrderDateTime
FROM Customer C, Orders o
WHERE C.CustomerNo=o.CustomerNo
AND o.OrderDateTime between TRUNC(SYSDATE) and SYSDATE
Click to expand...
from an ADO.Net Command object, it took minutes to return the data. This
lead me to believe that the hinted part of the SQL has been stripped of as
ADO.net considers it to be comments.
 
Back
Top