Re: Explain Plan Gives 0 Cost.

Joe Nardone <joe_at_access4.digex.net> wrote:
>Charles Dye (PC) (Charles_Dye_at_corp.dialog.com) wrote:
 

>: SQL> explain plan
>: 2 for
>: 3 select a.username, a.user_id, b.profile
>: 4 from a, b
>: 5 where a.user_id = b.user_id
>: 6 and b.created < sysdate
>: 7 and b.profile != 'DEFAULT'
>: 8 /
 

>: Explained.
 

>: SQL> select id, parent_id, position
>: 2 from plan_table;
 

>: ID PARENT_ID POSITION
>: ---------- ---------- ----------
>: 0 <<<<< Null here corresponds to Cost = 0
>: 1 0 0
>: 2 1 1
>: 3 2 1
>: 4 1 2
>: 5 4 1
 

>A null cost for the query comes up when running the rule based optimizer.
>When running cost based, you'd get a cost for the query.
 

>Joe

Ahah! Not exclusively true. If you are running (or trying to run) with the Cost based optimizer AND if you have not got STATISTICS for your tables (i.e. you didn't use the ANALYZE TABLE command on each table used in the query) then you can get a NULL value for cost.

This is not the same as a zero cost. A zero cost comes up as the number 0, or more likely as 0.012375651234.

Cheers.

 -Tom

-- 
Thomas B. Cox   tcox_at_netcom.com
Author of _Oracle Workgroup Server Handbook_, ISBN 0-07-881186-4