Re: Plan stability

The biggest problem with hints is that you cannot specify a full set - in particular there is no effective way to handling unnesting of subqueries.

For simple cases, you can put the tables in the main query in the 'correct' order and use the ORDERED hint, then name the indexes and
join mechanisms you want. But if you have any subqueries things break if Oracle unnests, so you need to hint the subqueries with NO_UNNEST.

On the other hand, unnesting may be desirable, in which case you have to rewrite the query in an unnested form, otherwise the unnested tables go to the top of the FROM list, and the ORDERED hint applies incorrectly.

There is no easy option until you get to v10 - where a couple of hint enhancements make it much simpler to specify your requirements.

Regards

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

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___November

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> Sent: Tuesday, December 02, 2003 8:44 AM

> Hi,
>
> my question is about the same, but more general. How can i force Oracle to
use my prefered way of explain plan and not use CBO's. I mean, apart from stored outlines, it somehow seems to complicated. I would like to say what order and join types it should use. But, try as I might, I many times cannot force Oracle to use my way, even though I know it is possible, for I saw this kind of explain plan for that specific query..
>
> I tried to use hints like ordered and use_hj etc. Can someone give some
examples of full set of hints for some simple queries?
>
> Thx,
> rw

-- 
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).