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