Re: Optimizer help, get query to run as good as with RULE hint

Absolutely identical plans, and traces - the only change was that the cost of the sort step went up. This was testing on a very simple plan too, trying to trade between

    sort (order by)
and an index driven order by - so nothing as complex as messing with a merge join.

If you're interested in the specific example, I'll post you the script offline to recreate the test case: I'll have to pull it from a different machine, though, which is why I can't post it right now.

BTW - I still believe quite firmly that hints are never ignored. They may be syntactically incorrect, there may be bugs, they may never become relevant; but I don't think they are ignored. However, I will agree that there are more and more grey areas in 9.2 where the increased scope for (internal) query rewrites is so extensive that it is becoming a lot harder to decide why a hint appears to have been ignored.

Regards

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

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August

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

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 16 February 2003 23:07
hint

>Did the increase in SORT_AREA_SIZE change the execution plan,
regardless of
>the use of hints (since certain hints can easily be ignored)? Larger
>SORT_AREA_SIZE might encourage the CBO to choose a SORT-MERGE join,
for
>example...
>

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

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).