Re: Rule Based Optimization

> What happens if you throw in a hint like FIRST_ROWS? Would the
> optimizer accept the hint ?

If you throw any hint to a query (except "RULE"), cost-based optimizing is triggered. In that case, since you probably don't have any stats for your tables, Oracle will try to make up these stats by itself, using criteria such as number of extents used by a table (to estimate its size), ... Oracle usually does a decent job at making up these stats (from what I've seen), but of course it would be much better to have real up-to-date stats if you intend to use CBO for some queries. If you decide to go that route (use hints and gather the stats), make sure that OPTIMIZER_MODE (for the instance running Siebel) is RULE (and not CHOOSE), or otherwise CBO will be used for all the queries (including the ones from the Siebel application) running against the database, and your Siebel TAM will slap your fingers.

Daniel Roy
IBM
Siebel/Oracle Consultant Received on Sun Apr 04 2004 - 20:56:59 CEST