Re: Is it safe to switch off cost based optimizing?

>Our primary use of Oracle is to extract several million records at a
>time from a set of tables, where extraction order is critical.
 

>Using the "order by" clause usually causes Oracle to do a full table
>sort, even if an index exists to generate the correct extraction order.
>So, in order to get correct extraction order, I over constrain the
>query with bogus "where" clauses until explain plan decides to use
>the index I want (which gives the correct extraction order).
 

>We are currently using the cost based optimizer, but would like to switch
>to rule based. Will switching strategies alter the execution plan for
>extraction (and screw up the extraction order)?
 

>We have NEVER run an analyze against any table.
 

>We are on Oracle 7.0.15.
 

>Any insight would be greatly appreciated.
 

>Marty (output_at_netcom.com)

The cost based optimizer will not be invoked unless at least one table has been analyzed so you should be OK.

Additional note. You can direct the CBO to use a specifc index without using over constraining where clauses. Use optimizer "hints". This approach will usually work for local queries. It will not work for remote or distributed queries.

David Roth

Carty Mailloux Consulting                        All opinions expressed
are my own.
UNIX Client/Server System Integrators      Please that them for what they
are 
(508) 392-1300x20                                  worth.