Hi, The /*+ push_subq */ hint sometimes works if the subquery is a small table. Good luck. Regards, Mike Thomas --- A Joshi <ajoshi977@xxxxxxxxx> wrote: > Hi, > I have a query joining three tables similar to the > following : > > select c.cust_name, o.order_number, > ro1.related_order_number > from order o, customer c, rel_orders ro1 > where c.cust_id = o.cust_id > and c.cust_type = 'OV' > and o.order_number = ro1.order_number > and ro1.related_order_number in (select > ro2.related_order_number from rel_orders ro2 > where > ro2.order_number = :v_order_number) > > Tables order and customer are huge (millions) and > rel_orders is very small (hundreads). I would like > the optimizer to go to the small table first then > get the order_number and go to orders table which > has index on order_number and then go to cust table > with cust_id(indexed). I tried the hint /*+ ordered > (ro2 ro1 o c) */ but it does not help. I even tried > to give the index hint for bigger tables but that > does not help. it either does full table scan or > index_full_scan. I tried several ways of writing the > query but it does not help. I tried without the > condition : and c.cust_type = 'OV'. No use. > Please note that there can be more than one > related_order_number for a order_number. Has someone > encountered a similar case and can someone help. > Thanks. > > > > > --------------------------------- > Do you Yahoo!? > Get better spam protection with Yahoo! Mail __________________________________ Do you Yahoo!? Get better spam protection with Yahoo! Mail. http://antispam.yahoo.com/tools ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------