Re: USE_NL with or without ORDERED

!! Please do not post Off Topic to this List !!

I think this is the best description I have seen so far of the way in which the optimiser has evolved.

Currently I believe that the optimizer HAS to obey hints if the hints can be applied to the paths it has been constrained to check. If this were not so, the new 'plan stability' feature of 8.1 could not work as it relies on storing hints in the database and applying them at run time.

However, in the latest versions of Oracle you need to ensure that Oracle is not
allowed to check any paths that do not
match your exact requirements - and
use_nl all by itself is not strict enough. Take a look at how many hints Oracle
pushes into user_outline_hints the next
time you try to produce a really simple
plan.

For your entertainment - here's an example even more surprising than the one you produced:

select

    /*+ use_nl(t2) */

        t1.cols, t2.cols
from

    table1 t1, table2 t2
where

    t1.id = t2.id
and t1.restriciton = {literal};

desired plan:

    nested loop

        full scan of t1
        indexed access into t2

Actual path:

    hash

        full scan of table 1
        full scan of table 2

How did Oracle manage to come up
with a plan that visited the tables in the right order, and STILL ignore the use_nl hint for getting into the second table.

Left as an exercise to the interested reader - but I will post the answer in a couple of days if anyone wants it.

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com Use spare CPU to assist in cancer research.

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 13 September 2001 16:46

|Picked this up on metalink - seems to cover your case well
|+++++++++++++++++
|Here is a good excerpt from Development on hints and the CBO...
|Query hints are used to restrict the number of alternative execution
plans
|the optimizer has to choose from. The optimizer will still pick the
cheapest
|plan from all of the alternatives considered. So for example, you
could get
|a situation where the optimizer picks a
|plan which does not contain a nested-loops join even though your
|query specified a USE_NL() hint. However, by combining hints you can
|restrict the optimizers search space to a single plan if you wish.
|For example the query
|select /*+ ordered use_nl(b) */ a.x from a, b where a.y = b.y;
|will only consider the plan "a NL b" because the combination of hints
|limits the search space to this single alternative. So if you have a
|query for which you want to fix the execution plan, you may need to
|use a combination of hints to restrict the search space to a single
|possibility.
|++++++++++++++++++++
|
|John
|

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).