Thank you all for your help : Mike, Dan Tow, Mark Bobak and Richard. The query is better after using the use_nl(ro2 ro1 o c) hint and eliminating the subquery.
Michael Thomas <mhthomas_at_yahoo.com> wrote:
Hi,
The /*+ push_subq */ hint sometimes works if the
subquery is a small table. Good luck.
Regards,
Mike Thomas
- A Joshi 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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
---------------------------------
Do you Yahoo!?
Get better spam protection with Yahoo! Mail
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Mon Mar 01 2004 - 21:43:46 CST
