| 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
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_at_singingsql.com]
Sent: Monday, March 01, 2004 6:20 PM
To: oracle-l_at_freelists.org
Cc: oracle-l_at_freelists.org
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 nonissue,
here, because perhaps the combination (order_number,
related_order_number) is unique, it's nice to know how to handle the moregeneral
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_numberand 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_at_singingsql.com
650-858-1557
www.singingsql.com
We make SQL sing!
Quoting "Bobak, Mark" <Mark.Bobak_at_il.proquest.com>:
> 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_at_yahoo.com]
> Sent: Monday, March 01, 2004 5:33 PM
> To: oracle-l_at_freelists.org
> 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
>
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_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 - 17:27:29 CST
 |
 |