索引扫描成本计算

SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
 
SQL> create table t as select * from dba_objects;

Table created.

SQL> create index idx_t on t(object_id);

Index created.

SQL> BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'TEST',
  3                                  tabname          => 'T',
  4                                  estimate_percent => 100,
  5                                  method_opt       => 'for all columns size auto',
  6                                  degree           => DBMS_STATS.AUTO_DEGREE,
  7                                  cascade          => TRUE);
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> select leaf_blocks,blevel,clustering_factor from dba_indexes where index_name='IDX_T';

LEAF_BLOCKS     BLEVEL CLUSTERING_FACTOR
----------- ---------- -----------------
        161          1              1636
        
SQL> select a.column_name,
  2         b.num_rows,
  3         a.num_distinct,
  4         a.num_nulls,
  5         utl_raw.cast_to_number(high_value) high_value,
  6         utl_raw.cast_to_number(low_value) low_value,
  7         (b.num_rows-a.num_nulls) "NUM_ROWS-NUM_NULLS",
  8          utl_raw.cast_to_number(high_value)- utl_raw.cast_to_number(low_value)"HIGH_VALUE-LOW_VALUE",
  9         density,
 10         a.histogram,
 11         a.num_buckets
 12    from dba_tab_col_statistics a, dba_tables b
 13   where a.owner = b.owner
 14     and a.table_name = b.table_name
 15     and a.owner ='TEST'
 16     and a.table_name = upper('T')
 17     and a.column_name='OBJECT_ID';

COLUMN_NAME            NUM_ROWS NUM_DISTINCT  NUM_NULLS HIGH_VALUE  LOW_VALUE NUM_ROWS-NUM_NULLS HIGH_VALUE-LOW_VALUE    DENSITY HISTOGRAM       NUM_BUCKETS
-------------------- ---------- ------------ ---------- ---------- ---------- ------------------ -------------------- ---------- --------------- -----------
OBJECT_ID                 72469        72469          0      74664          2              72469                74662 .000013799 NONE                      1

SQL> alter session set optimizer_features_enable='9.2.0';

Session altered.

SQL> select owner from t where object_id<1000;

942 rows selected.

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   969 | 10659 |    26 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   969 | 10659 |    26 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |   969 |       |     4 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"<1000)

Note
-----
   - cpu costing is off (consider enabling it)

cost =
 blevel +
 celiling(leaf_blocks *effective index selectivity) +
 celiling(clustering_factor * effective table selectivity)

SQL> select 1+ceil(161*(1000-2)/74662)+ceil(1636*(1000-2)/74662) from dual;

1+CEIL(161*(1000-2)/74662)+CEIL(1636*(1000-2)/74662)
----------------------------------------------------
                                                  26
                                                  
SQL> alter session set  optimizer_features_enable='11.2.0.1';

Session altered.
 
SQL>  select owner from t where object_id<1000;

942 rows selected.

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   969 | 10659 |    26   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   969 | 10659 |    26   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |   969 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"<1000)