Re: Statistics and optimizer

""Ann Myhre"" <annmyhre_at_hotmail.com> wrote in message news:OE7b4uFM5NKGfVNMnfi00002744_at_hotmail.com... Does anyone know if optimizer will use the costbased method if no statistcis is gathered (ANALYZE TABLE etc.)? I have information saying that if no statistics is gathered, optimizer will choose the rulebased method, and another source saying that then will optimizer (if set to CHOOSE) choose some default values includes in Oracle. Help anyone?

Ann

First of all: could you please refrain from posting in HTML. Many newsreaders can not deal with it.
IIRC:
If init.ora optimizer_goal = first_rows or all_rows cost based will be used, whether statistics exist or not. If no statistics exist, they will be estimated If init.ora parameter optimizer_goal = choose if any of the tables involved *has* statistics, CBO will be used, if *none* has statistics, RBO will be used.
However, please move away from using RBO. It hasn't been updated since 1993, and when you stick to RBO you are missing out more and more new and more efficient access paths like hash_join and index_fast_full_scan, not to forget parallel query.

Regards,

Sybrand Bakker, Oracle DBA Received on Fri May 04 2001 - 04:39:56 CDT