Re: ** SQL tuning question

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 2 Mar 2004 08:25:10 -0000
Message-ID: <005001c4002f$e5e109d0$6702a8c0@Primary>

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_at_singingsql.com> To: <oracle-l_at_freelists.org> Cc: <oracle-l_at_freelists.org> 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_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 Tue Mar 02 2004 - 02:22:07 CST