索引扫描成本计算

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                      1SQL> 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.07Execution 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.05Execution 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)