> What's the objection to using stored outlines? That's the > Oracle-provided mechanism for forcing queries to use a particular > plan.
The problem is that I have to first analyze tables with real data for CBO to create plan I find useful (like using specific indexes etc.) and then to store it. When writing query I usually know what kind of data it will be there even before it is loaded there (and CBO cannot). >> 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? > > A properly formed hint will cause the CBO to consider the hinted path > to be less costly than it would otherwise consider it, but hints do > not force a query to use that particular plan. For a moderately > complicated query, you'd potentially need a fair number of hints to > get things working the way you want. If the statistics of the table > changed, though, your carefully hinted query might well decide to > take another path. Even if things work, adding hints-- particularly > adding multiple hints-- to a query significantly increases the > maintenance costs as future developers have to unravel what all the > hints are doing, why they're doing it, whether any hints need to be > changed as a result of the modifications, whether future changes to > the CBO or new Oracle functionality should cause the ideal plan to > change, etc. That's why I am asking. I was thinking of some simple way of telling oracle - scan this index first, do full scan of other table, use hash join then etc. If you have explain plain with 5 steps, why cannot I write these 5 steps together with query so Oracle takes it for granted that this is the best way to do it? > > If you want to force Oracle to use a particular plan, plan stability > is orders of magnitude easier! > > Justin Cave -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wartiak Rastislav INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
