| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: ** SQL tuning question
I think you can write the query below as:
select c.cust_name, o.order_number, ro1.related_order_number from order o, customer c, rel_orders ro1, rel_orders ro2
where c.cust_id = o.cust_id and c.cust_type = 'OV' and o.order_number = ro1.order_numberand ro1.related_order_number = ro2.related_order_number and ro2.order_number = :v_order_number
This change may or may not make life easier for the optimisor. Otherwise, start looking at using the USE_NL hint if the optimisor isn't aware of how selective the order_number field is - I think the hint would be USE_NL(ro2, ro1). Hopefully though, after rewriting the query you won't have to use a hint at all (I prefer to not use hints except where necessary).
Good luck and let us know how it works.
Regards,
Mark.
A Joshi
<ajoshi977_at_yahoo.c To: oracle-l_at_freelists.org
om> cc:
Sent by: Subject: ** SQL tuning question
oracle-l-bounce_at_fr
eelists.org
02/03/2004 09:32
Please respond to
oracle-l
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_numberand 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
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone.
In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999.
Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- 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 - 16:38:44 CST
 |
 |