cluster factor(聚簇因子值)计算

SQL> drop table t1 purge;

Table dropped.

create table t1(
n1 number,
 v2 varchar2(3500),
 v3 varchar2(3500)
 );

Table created.

SQL> create sequence seq001 start with 1;

Sequence created.

SQL> insert into t1 values(seq001.nextval,lpad('a',3400,'x'),lpad('a',3400,'x'));

1 row created.

SQL> l
  1* insert into t1 values(seq001.nextval,lpad('a',3400,'x'),lpad('a',3400,'x'))
SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select count(1) from t1;

  COUNT(1)
----------
         8
create index idx_t1_n1 on t1(n1);
         
SQL> exec dbms_stats.gather_table_stats('AIKI','T1',cascade=>true,method_opt=>'for all indexed columns size 1',estimate_percent=>100);

 
Index                      Tree Leaf       Distinct         Number Leaf Blocks Data Blocks      Cluster Global User           Sample Date
Name            Unique    Level Blks           Keys        of Rows     Per Key     Per Key       Factor Stats  Stats            Size MM-DD-YYYY
--------------- --------- ----- ---- -------------- -------------- ----------- ----------- ------------ ------ ------ -------------- ----------
IDX_T1_N1       NONUNIQUE     0    1              8              8           1           1            8 YES    NO                  8 05-14-2014

SQL>   select DBMS_ROWID.ROWID_ROW_NUMBER(rowid),dbms_rowid.rowid_block_number(rowid) from t1;

DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------------------------------- ------------------------------------
                                 0                                37555
                                 0                                37556
                                 0                                37557
                                 0                                37558
                                 0                                37559
                                 0                                37563
                                 0                                37564
                                 0                                37565
 
 
 SQL> drop table t2 purge;
SQL> 
SQL> 
create table t2 (
 n1 number,
 v2 varchar2(4000),
v3 varchar2(4000)
 );
  
SQL> insert into t2 values(1,'a','c');

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> create index idx_t2_n1 on t2(n1);

Index created.

SQL> exec dbms_stats.gather_table_stats('AIKI','T2',cascade=>true,method_opt=>'for all indexed columns size 1',estimate_percent=>100);

PL/SQL procedure successfully completed.

Index                      Tree Leaf       Distinct         Number Leaf Blocks Data Blocks      Cluster Global User           Sample Date
Name            Unique    Level Blks           Keys        of Rows     Per Key     Per Key       Factor Stats  Stats            Size MM-DD-YYYY
--------------- --------- ----- ---- -------------- -------------- ----------- ----------- ------------ ------ ------ -------------- ----------
IDX_T2_N1       NONUNIQUE     0    1              1              8           1           1            1 YES    NO                  8 05-14-2014

SQL> select DBMS_ROWID.ROWID_ROW_NUMBER(rowid),dbms_rowid.rowid_block_number(rowid) from t2;

DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------------------------------- ------------------------------------
                                 0                                37580
                                 1                                37580
                                 2                                37580
                                 3                                37580
                                 4                                37580
                                 5                                37580
                                 6                                37580
                                 7                                37580

 可以看到：cluster factor 的值等于索引指向的表块的差异值+1

第1个例子的表块共有8个块，cluster factor的值:8-1+1=8

第2个例子的表块共有1个块，cluster factor的值:1-1+1=1