RE: ** SQL tuning question
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 1 Mar 2004 18:30:42 -0500
Dan,
Good catch, particularly since in the original posting it said:
"Please note that there can be more than one related_order_number for a
order_number."
(That's what I get for re-writing SQL without having a sample dataset!)
-Mark
-----Original Message-----
From: Dan Tow [mailto:dantow@xxxxxxxxxxxxxx]
Sent: Monday, March 01, 2004 6:20 PM
To: oracle-l@xxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: ** SQL tuning question
Mark's rewrite can create duplicate rows if related_order_number is non-unique
for a given order_number. (These duplicates would be eliminated by the IN
subquery, which would do a sort-unique on its results.) Even if this is a non-
issue, here, because perhaps the combination (order_number,
related_order_number) is unique, it's nice to know how to handle the more-
general case. I've seen cases where it is hard to force the CBO to drive from
the IN subquery, out. The ordered hint can only refer to the order of the FROM
clause of that query block (There is no "ordered(alias1, alias2, ...)" hint
syntax - ordered *only* refers to the order of the FROM clause of that query
block - this is a serious limitation of the hint, especially where you can't
influence the order of the FROM clause. The nearest thing to an ordered hint
that doesn't require rewriting FROM clauses is the LEADING() hint, which at
least allows you to specify the leading table in the order.) Here's a syntax
I've found works well for forcing the join order when you can fully control the
SQL:
select /*+ ordered */
c.cust_name, o.order_number, ro1.related_order_number
from
(select distinct ro2.related_order_number related_order_number from
rel_orders ro2 where ro2.order_number = :v_order_number) rov,
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 = rov.related_order_number
If necessary, you could add use_nl(ro1 o c) and as many index hints as needed
to get the optimizer to follow the indexed path through the join keys, and
perhaps a hint to reach ro2 though the index on order_number following the
second "select", if needed, although this table is small enough that it
probably matters little how you reach this driving table.
Dan Tow
dantow@xxxxxxxxxxxxxx
650-858-1557
www.singingsql.com
We make SQL sing!
Quoting "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>:
> 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
>
>
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
Other related posts:
