in principle

keywords -- in principle.

In a perfect world. If the numbers were always dead on accurate. Yes.

But they aren't. They get better and better -- but they are't.

And the real confusion comes in with something like this:

ops$tkyte@ORA9IR2> /*
DOC>drop table t;
DOC>create table t as select * from all_objects;
DOC>create index t_idx on t(object_id);
DOC>
DOC>exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
DOC>*/
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> alter session set optimizer_index_caching =0;

Session altered.

ops$tkyte@ORA9IR2> alter session set optimizer_index_cost_adj =100;

Session altered.

ops$tkyte@ORA9IR2> select * from t where object_id < 100;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=61 Card=66 Bytes=6138)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=61 Card=66 Bytes=6138)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=66)



ops$tkyte@ORA9IR2> alter session set optimizer_index_caching =90;

Session altered.

ops$tkyte@ORA9IR2> alter session set optimizer_index_cost_adj =10;

Session altered.

ops$tkyte@ORA9IR2> select * from t where object_id < 100;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=66 Bytes=6138)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=7 Card=66 Bytes=6138)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=66)



ops$tkyte@ORA9IR2> set autotrace off


Did we really make this query run faster by setting those parameters? No, we changed a bunch of numbers fed into a mathematical model and came out with different numbers.

If the optimizer was perfect in its guesses and assumptions (as it gets closer and closer to that), the cost will be a more accurate predictor of time. 10g with cpu costing gets a step closer yet.

But for every case where you give me a pair of semantically equivalent queries that the lower cost one runs faster, I'll give you a pair where the converse is true (and since the set of SQL queries is inifinite.... I cannot prove that :)


(jonathan knows a *lot* about that CBO doesn't he, scary sometimes...)