Re: ** SQL tuning question
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 Mar 2004 08:25:10 -0000
Notes in-line.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
March 2004 Hotsos Symposium - The Burden of Proof
Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial
April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar
----- Original Message -----
From: "Dan Tow" <dantow@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, March 01, 2004 11:19 PM
Subject: RE: ** SQL tuning question
> 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.)
Good news - there is a new improved leading() hint which
does exactly what the OP's illegal ordered hint was trying to do:
leading (t4, t1, t2, t3)
will direct the order of the four aliased tables.
>
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
For versions of Oracle that won't unnest automatically,
an UNNEST hint in the subquery will rewrite the query
to produce this inline view method - if it's legal. (But the
rewritten query goes to the top of the FROM clause, which
is why you can get nasty results when you upgrade and a query
with an ORDERED hint and a subquery suddenly unnests).
> 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
>
----------------------------------------------------------------
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:
