RE: ** SQL tuning question
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 1 Mar 2004 17:37:56 -0500
Try this instead:
select /*+ ordered use_nl(ro1) use_nl(o) use_nl(c) */
c.cust_name, o.order_number, ro1.related_order_number
from rel_orders ro2, rel_orders ro1, order o, customer c
where c.cust_id = o.cust_id
and c.cust_type = 'OV'
and o.order_number = ro1.order_number
and ro1.related_order_number = ro2.related_order_number
and ro2.order_number = :v_order_number
-----Original Message-----
From: A Joshi [mailto:ajoshi977@xxxxxxxxx]
Sent: Monday, March 01, 2004 5:33 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: ** SQL tuning question
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!
<http://us.rd.yahoo.com/mailtag_us/*http://antispam.yahoo.com/tools> Mail
Other related posts:
