集群因子 例子

测试:create table t1 as select trunc((rownum-1)/100) id,                  rpad(rownum,100) t_pad                  from dba_source              where rownum<100000;create index t1_idx1 on t1(id);BEGIN  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'HW',                                tabname          => 'T1',                                estimate_percent => 100,                                method_opt       => 'for all columns size skewonly',                                no_invalidate    => FALSE,                                degree           => 8,                                cascade          => TRUE);END;SQL> select index_name,clustering_factor from user_indexes where table_name='T1';  INDEX_NAME       CLUSTERING_FACTOR------------------------------ -----------------T1_IDX1     1536SQL>  select count(distinct dbms_rowid.rowid_block_number(rowid)) from t1;COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))---------------------------------------------------       1536SQL> select * from (select id,count(*) from t1 group by id order by id,count(*)) where rownum<10;ID   COUNT(*)---------- ---------- 0  100 1  100 2  100 3  100 4  100 5  100 6  100 7  100 8  1009 rows selected.SQL> select * from t1 where id=1;100 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 2623418078---------------------------------------------------------------------------------------| Id  | Operation    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |      |   100 | 10500 |     3(0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T1      |   100 | 10500 |     3(0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN    | T1_IDX1 |   100 |       |     1(0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("ID"=1)Statistics----------------------------------------------------------  1  recursive calls  0  db block gets 19  consistent gets  0  physical reads  0  redo size      12649  bytes sent via SQL*Net to client586  bytes received via SQL*Net from client  8  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)100  rows processed集群因子接近表的块数,索引效率较高。2)create table t2 as select mod(rownum,100) id,                  rpad(rownum,100) t_pad                  from dba_source              where rownum<100000;     create index t1_idx2 on t2(id);         BEGIN  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'HW',                                tabname          => 'T2',                                estimate_percent => 100,                                method_opt       => 'for all columns size skewonly',                                no_invalidate    => FALSE,                                degree           => 8,                                cascade          => TRUE);END;SQL> select index_name,clustering_factor from user_indexes where table_name='T2';INDEX_NAME       CLUSTERING_FACTOR------------------------------ -----------------T1_IDX2    99999SQL> select count(*) from t2;  COUNT(*)----------     99999SQL> select count(*) from t2 where id=1;  COUNT(*)----------      1000SQL> select 1000/99999 * 100 from dual;1000/99999*100--------------       1.00001这个数据比例应该走索引啊！SQL> select * from t2 where id=1;1000 rows selected.SQL> set pagesize 200SQL> set autot traceSQL> select * from t2 where id=1;1000 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1513984157--------------------------------------------------------------------------| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  | |  1000 |   101K|   423   (1)| 00:00:06 ||*  1 |  TABLE ACCESS FULL| T2 |  1000 |   101K|   423   (1)| 00:00:06 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("ID"=1)Statistics----------------------------------------------------------  0  recursive calls  0  db block gets       1588  consistent gets       1517  physical reads  0  redo size     118709  bytes sent via SQL*Net to client       1246  bytes received via SQL*Net from client 68  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)       1000  rows processed确走全表扫描了,逻辑读为1588强制走索引的逻辑读:SQL> select /*+ index(t2 T1_IDX2)*/   * from t2 where id=1;1000 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1418564783---------------------------------------------------------------------------------------| Id  | Operation    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |      |  1000 |   101K|  1002(0)| 00:00:13 ||   1 |  TABLE ACCESS BY INDEX ROWID| T2      |  1000 |   101K|  1002(0)| 00:00:13 ||*  2 |   INDEX RANGE SCAN    | T1_IDX2 |  1000 |       |     2(0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("ID"=1)Statistics----------------------------------------------------------  0  recursive calls  0  db block gets       1071  consistent gets       1004  physical reads  0  redo size     121909  bytes sent via SQL*Net to client       1246  bytes received via SQL*Net from client 68  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)       1000  rows processed