oracle聚簇因子
聚簇因子是 Oracle 统计信息中在CBO优化器模式下用于计算cost的参数之一，决定了当前的SQL语句是否走索引，还是全表扫描以及是否作为嵌套连接外部表等。如此这般，那到底什么是聚簇因子，那些情况下会影响到聚簇因子，以及如何提高聚簇因子？本文将对此展开描述。
Execution Plan
----------------------------------------------------------
Plan hash value: 1434024551
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
2028 consistent gets
12 physical reads
0 redo size
162538 bytes sent via SQL*Net to client
4183 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5001 rows processed
--原始表上的查询的cost为03, consistent gets与physical reads分别为2028,12
SQL> select * from t4 where id between 10000 and 15000;
Execution Plan
----------------------------------------------------------
Plan hash value: 625722358
Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
718 consistent gets
0 physical reads
0 redo size
162538 bytes sent via SQL*Net to client
4183 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
5001 rows processed
--新创建的表的cost 为01, consistent gets与physical reads分别为718,0. 低于原表的开销
-->可以将原始表t3上的数据删除(truncate)，删除前禁用依赖于该表的所有约束，然后将t4的数据使用order by插入到t3
SQL> truncate table t3;
1、堆表的存储方式
Oralce 数据库系统中最普通，最为常用的即为堆表。
堆表的数据存储方式为无序存储，也就是任意的DML操作都可能使得当前数据块存在可用的空闲空间。
处于节省空间的考虑，块上的可用空闲空间会被新插入的行填充，而不是按顺序填充到最后被使用的块上。
上述的操作方式导致了数据的无序性的产生。
当创建索引时，会根据指定的列按顺序来填充到索引块，缺省的情况下为升序。
新建或重建索引时，索引列上的顺序是有序的，而表上的顺序是无序的，也就是存在了差异，即表现为聚簇因子。
2、什么是聚簇因子(clustering factor/CF)
聚簇因子是基于表上索引列上的一个值，每一个索引都有一个聚簇因子。
用于描述索引块上与表块上存储数据在顺序上的相似程度，也就说表上的数据行的存储顺序与索引列上顺序是否一致。
在全索引扫描中，CF的值基本上等同于物理I/O或块访问数，如果相同的块被连续读，则Oracle认为只需要1次物理I/O。
好的CF值接近于表上的块数，而差的CF值则接近于表上的行数。
聚簇因子在索引创建时就会通过表上存存在的行以及索引块计算获得。
3、Oracle 如何计算聚簇因子
执行或预估一次全索引扫描。
检查索引块上每一个rowid的值，查看是否前一个rowid的值与后一个指向了相同的数据块，如果指向了不相同的数据块则CF的值增加1。
当索引块上的每一个rowid被检查完毕，即得到最终的CF值。
4、聚簇因子图示
a、良好的索引与聚簇因子的情形
b、良好的索引、差的聚簇因子的情形
c、差的索引、差的聚簇因子的情形
5、影响聚簇因子的情形
当插入到表的数据与索引的顺序相同时，可以提高聚簇因子(接近表上的块数)。
因此，任意影响该顺序的情形都将导致索引列上的聚簇因子变差。
如列的顺序，反向索引，空闲列表或空闲列表组。
6、提高聚簇因子
堆表的数据存储是无序存储，因此需要使无序变为有序。下面是提高聚簇因子的办法。
a、对于表上的多个索引以及组合索引的情形，索引的创建应考虑按应该按照经常频繁读取的大范围数据的读取顺序来创建索引。
b、定期重构表(针对堆表)，也就是使得表与索引上的数据顺序更接近。注意，是重构表，而不是重建索引。
重建索引并不能显剧提高CF的值，因为索引列通常是有序的，无序的是原始表上的数据。
提取原始表上的数据到一个临时表，禁用依赖于该表的相关约束，truncate原始表，再将临时表的数据按索引访问顺序填充到原始表。
c、使用聚簇表来代替堆表。
7、实战聚簇因子随索引结构变化的情形
a、演示环境
SQL> select t.INSTANCE_NAME,t.INSTANCE_NUMBER,t.VERSION from v$instance t;
SQL> select t.INSTANCE_NAME,t.INSTANCE_NUMBER,t.VERSION from v$instance t;
INSTANCE_NAME INSTANCE_NUMBER VERSION
---------------- --------------- -----------------
db01 1 11.2.0.4.0
b、列顺序对CF的影响
--列顺序指索引列值顺序与表中的列值的顺序，一致，则CF良好，不一致，CF较差。
SQL> create table t1 as select * from dba_objects order by object_name;
---------------- --------------- -----------------
db01 1 11.2.0.4.0
b、列顺序对CF的影响
--列顺序指索引列值顺序与表中的列值的顺序，一致，则CF良好，不一致，CF较差。
SQL> create table t1 as select * from dba_objects order by object_name;
Table created.
SQL> create index idx_obj_name on t1(object_name);
Index created.
SQL> create index idx_obj_id on t1(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats('sys','T1',cascade=>true);
SQL> exec dbms_stats.gather_table_stats('sys','T1',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select T.TABLE_NAME, --表名
2 T.BLOCKS, --表占块数
3 T.NUM_ROWS, --表记录数
4 T1.INDEX_NAME, --索引名
5 T1.LEAF_BLOCKS, --索引叶子块数量
6 T1.CLUSTERING_FACTOR --聚簇因子
7 from dba_tables t, dba_indexes t1
8 where t.TABLE_NAME = t1.table_name
9 and t.table_name = 'T1';
TABL BLOCKS NUM_ROWS INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR
---- ---------- ---------- -------------------- ----------- -----------------
T1 1241 87006 IDX_OBJ_ID 193 46899
T1 1241 87006 IDX_OBJ_NAME 434 1241
--从上面的查询可以看出，索引IDX_OBJ_NAME的聚簇因子等于表占的块数，是一个良好的CF值，因为object_name列是有 序插入的。
--而索引IDX_OBJ_ID上的CF接近表上行数的一半，说明该索引上的CF值不是很理想，因为object_id在插入到table时是无序的。
--从上可知，一个表只能有一种有序的方式来组织数据。因此对于多个索引的表，且顺序按照非插入时的顺序时，则其他索引上的聚簇因子很难获得理想的值。
c、组合索引对CF的影响
--对于组合索引，列的顺序影响聚簇因子的大小
--我们创建如下组合索引
SQL> create index idx_obj_name_id on t1(object_name,object_id);
SQL> select T.TABLE_NAME, --表名
2 T.BLOCKS, --表占块数
3 T.NUM_ROWS, --表记录数
4 T1.INDEX_NAME, --索引名
5 T1.LEAF_BLOCKS, --索引叶子块数量
6 T1.CLUSTERING_FACTOR --聚簇因子
7 from dba_tables t, dba_indexes t1
8 where t.TABLE_NAME = t1.table_name
9 and t.table_name = 'T1';
TABL BLOCKS NUM_ROWS INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR
---- ---------- ---------- -------------------- ----------- -----------------
T1 1241 87006 IDX_OBJ_ID 193 46899
T1 1241 87006 IDX_OBJ_NAME 434 1241
--从上面的查询可以看出，索引IDX_OBJ_NAME的聚簇因子等于表占的块数，是一个良好的CF值，因为object_name列是有 序插入的。
--而索引IDX_OBJ_ID上的CF接近表上行数的一半，说明该索引上的CF值不是很理想，因为object_id在插入到table时是无序的。
--从上可知，一个表只能有一种有序的方式来组织数据。因此对于多个索引的表，且顺序按照非插入时的顺序时，则其他索引上的聚簇因子很难获得理想的值。
c、组合索引对CF的影响
--对于组合索引，列的顺序影响聚簇因子的大小
--我们创建如下组合索引
SQL> create index idx_obj_name_id on t1(object_name,object_id);
Index created.
SQL> create index idx_obj_id_name on t1(object_id,object_name);
Index created.
SQL> exec dbms_stats.gather_table_stats('sys','T1',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select T.TABLE_NAME,
2 T.BLOCKS,
3 T.NUM_ROWS,
4 T1.INDEX_NAME,
5 T1.LEAF_BLOCKS,
6 T1.CLUSTERING_FACTOR
7 from dba_tables t, dba_indexes t1
8 where t.TABLE_NAME = t1.table_name
9 and t.table_name = 'T1';
SQL> select T.TABLE_NAME,
2 T.BLOCKS,
3 T.NUM_ROWS,
4 T1.INDEX_NAME,
5 T1.LEAF_BLOCKS,
6 T1.CLUSTERING_FACTOR
7 from dba_tables t, dba_indexes t1
8 where t.TABLE_NAME = t1.table_name
9 and t.table_name = 'T1';
TABL BLOCKS NUM_ROWS INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR
---- ---------- ---------- -------------------- ----------- -----------------
T1 1241 87006 IDX_OBJ_ID_NAME 494 46899
T1 1241 87006 IDX_OBJ_NAME_ID 494 1681
T1 1241 87006 IDX_OBJ_ID 193 46899
T1 1241 87006 IDX_OBJ_NAME 434 1241
--从上面的结果可知，
--新创建的组合索引，IDX_OBJ_NAME_ID(object_name, object_id),object_name是前导列，因此CF值尽管比单列是大，依然表现良好。
--而索引IDX_OBJ_ID_NAME(object_id, object_name)，object_id作为前导列，CF值与单列索引I_OBJ_ID相同。
--上面的四个索引来看，无论是单列还是符合索引，当索引列(leaf)的顺序接近于表上行的顺序，CF表现良好。
d、反向索引对CF的影响
--反转索引主要是重新分布索引值，也就是将相连比较紧密地索引键值分散到不同或相距比较远的快上以避免竞争。
--下面基于表t来新创建表t2
SQL> create table t2 nologging as select * from t1;
---- ---------- ---------- -------------------- ----------- -----------------
T1 1241 87006 IDX_OBJ_ID_NAME 494 46899
T1 1241 87006 IDX_OBJ_NAME_ID 494 1681
T1 1241 87006 IDX_OBJ_ID 193 46899
T1 1241 87006 IDX_OBJ_NAME 434 1241
--从上面的结果可知，
--新创建的组合索引，IDX_OBJ_NAME_ID(object_name, object_id),object_name是前导列，因此CF值尽管比单列是大，依然表现良好。
--而索引IDX_OBJ_ID_NAME(object_id, object_name)，object_id作为前导列，CF值与单列索引I_OBJ_ID相同。
--上面的四个索引来看，无论是单列还是符合索引，当索引列(leaf)的顺序接近于表上行的顺序，CF表现良好。
d、反向索引对CF的影响
--反转索引主要是重新分布索引值，也就是将相连比较紧密地索引键值分散到不同或相距比较远的快上以避免竞争。
--下面基于表t来新创建表t2
SQL> create table t2 nologging as select * from t1;
Table created.
SQL> create index idx_obj_name_reverse on t2(object_name) reverse;
SQL> create index idx_obj_name_reverse on t2(object_name) reverse;
Index created.
SQL> exec dbms_stats.gather_table_stats('sys','T2',cascade=>true);
SQL> exec dbms_stats.gather_table_stats('sys','T2',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select T.TABLE_NAME,
2 T.BLOCKS,
3 T.NUM_ROWS,
4 T1.INDEX_NAME,
5 T1.LEAF_BLOCKS,
6 T1.CLUSTERING_FACTOR
7 from dba_tables t, dba_indexes t1
8 where t.TABLE_NAME = t1.table_name
9 and t.table_name = 'T2';
SQL> select T.TABLE_NAME,
2 T.BLOCKS,
3 T.NUM_ROWS,
4 T1.INDEX_NAME,
5 T1.LEAF_BLOCKS,
6 T1.CLUSTERING_FACTOR
7 from dba_tables t, dba_indexes t1
8 where t.TABLE_NAME = t1.table_name
9 and t.table_name = 'T2';
TABL BLOCKS NUM_ROWS INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR
---- ---------- ---------- -------------------- ----------- -----------------
T2 1241 87006 IDX_OBJ_NAME_REVERSE 434 50710
--上面创建的反向索引的CF较之前的都要大，因索引键上的值是反向的，也就是说是无序的。
--在段空间管理基于手动管理的方式下，如果使用freelist可以避免段操作上DML的竞争，但索引列上将具有较比较糟糕的聚簇因子(演示省略)
---- ---------- ---------- -------------------- ----------- -----------------
T2 1241 87006 IDX_OBJ_NAME_REVERSE 434 50710
--上面创建的反向索引的CF较之前的都要大，因索引键上的值是反向的，也就是说是无序的。
--在段空间管理基于手动管理的方式下，如果使用freelist可以避免段操作上DML的竞争，但索引列上将具有较比较糟糕的聚簇因子(演示省略)
8、实战聚簇因子随DML变化的情形
a、创建演示环境
--准备百万数据
SQL> create table t3 (id number,name varchar2(40));
--准备百万数据
SQL> create table t3 (id number,name varchar2(40));
Table created.
SQL> alter table t3 add constraint pk_t3_id primary key(id);
Table altered.
SQL> declare
2 i number;
3 begin
4 for i in 1 .. 1000000 loop
5 insert into t3 values (i,'test'||i);
6 end loop
7 commit;
8 end;
9 /
2 i number;
3 begin
4 for i in 1 .. 1000000 loop
5 insert into t3 values (i,'test'||i);
6 end loop
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('sys','T3',cascade=>true);
SQL> exec dbms_stats.gather_table_stats('sys','T3',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select T.TABLE_NAME,
2 T.BLOCKS,
3 T.NUM_ROWS,
4 T1.INDEX_NAME,
5 T1.LEAF_BLOCKS,
6 T1.CLUSTERING_FACTOR
7 from dba_tables t, dba_indexes t1
8 where t.TABLE_NAME = t1.table_name
9 and t.table_name = 'T3';
2 T.BLOCKS,
3 T.NUM_ROWS,
4 T1.INDEX_NAME,
5 T1.LEAF_BLOCKS,
6 T1.CLUSTERING_FACTOR
7 from dba_tables t, dba_indexes t1
8 where t.TABLE_NAME = t1.table_name
9 and t.table_name = 'T3';
TABL BLOCKS NUM_ROWS INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR
---- ---------- ---------- -------------------- ----------- -----------------
T3 2875 1000000 PK_T3_ID 1875 2874
---- ---------- ---------- -------------------- ----------- -----------------
T3 2875 1000000 PK_T3_ID 1875 2874
b、模拟DML操作
--创建一个临时表来存储将要从表t3删除的记录
SQL> create table t4 nologging as select * from t3 where id>=10000 and id <=200000;
--创建一个临时表来存储将要从表t3删除的记录
SQL> create table t4 nologging as select * from t3 where id>=10000 and id <=200000;
Table created.
SQL> delete from t3 nologging where id between 10000 and 200000;
SQL> delete from t3 nologging where id between 10000 and 200000;
190001 rows deleted.
SQL> commit;
Commit complete.
-->查看表与索引相关信息(从下面的查询结果可知，删除记录并不使得CF发生变化)
SQL> select T.TABLE_NAME,
2 T.BLOCKS,
3 T.NUM_ROWS,
4 T1.INDEX_NAME,
5 T1.LEAF_BLOCKS,
6 T1.CLUSTERING_FACTOR
7 from dba_tables t, dba_indexes t1
8 where t.TABLE_NAME = t1.table_name
9 and t.table_name = 'T3';
-->查看表与索引相关信息(从下面的查询结果可知，删除记录并不使得CF发生变化)
SQL> select T.TABLE_NAME,
2 T.BLOCKS,
3 T.NUM_ROWS,
4 T1.INDEX_NAME,
5 T1.LEAF_BLOCKS,
6 T1.CLUSTERING_FACTOR
7 from dba_tables t, dba_indexes t1
8 where t.TABLE_NAME = t1.table_name
9 and t.table_name = 'T3';
TABL BLOCKS NUM_ROWS INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR
---- ---------- ---------- -------------------- ----------- -----------------
T3 2875 1000000 PK_T3_ID 1875 2874
SQL> exec dbms_stats.gather_table_stats('sys','T3',cascade=>true);
---- ---------- ---------- -------------------- ----------- -----------------
T3 2875 1000000 PK_T3_ID 1875 2874
SQL> exec dbms_stats.gather_table_stats('sys','T3',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select T.TABLE_NAME,
2 T.BLOCKS,
3 T.NUM_ROWS,
4 T1.INDEX_NAME,
5 T1.LEAF_BLOCKS,
6 T1.CLUSTERING_FACTOR
7 from dba_tables t, dba_indexes t1
8 where t.TABLE_NAME = t1.table_name
9 and t.table_name = 'T3';
SQL> select T.TABLE_NAME,
2 T.BLOCKS,
3 T.NUM_ROWS,
4 T1.INDEX_NAME,
5 T1.LEAF_BLOCKS,
6 T1.CLUSTERING_FACTOR
7 from dba_tables t, dba_indexes t1
8 where t.TABLE_NAME = t1.table_name
9 and t.table_name = 'T3';
TABL BLOCKS NUM_ROWS INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR
---- ---------- ---------- -------------------- ----------- -----------------
T3 2875 809999 PK_T3_ID 1520 2338
-->接下来将删除的数据插入到t3以模拟表上新增数据，分3次插入，以使得id变得无序
SQL> insert into t3 nologging select * from t4 where id between 150001 and 200000;
---- ---------- ---------- -------------------- ----------- -----------------
T3 2875 809999 PK_T3_ID 1520 2338
-->接下来将删除的数据插入到t3以模拟表上新增数据，分3次插入，以使得id变得无序
SQL> insert into t3 nologging select * from t4 where id between 150001 and 200000;
50000 rows created.
SQL> commit;
Commit complete.
SQL> insert into t3 nologging select * from sys.t4 where id between 10000 and 150000 and mod(id,7)=0;
20000 rows created.
SQL> commit;
Commit complete.
SQL> insert into t3 nologging select * from sys.t4 where id between 10000 and 150000 and mod(id,7)<>0;
120001 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('sys','T3',cascade=>true);
PL/SQL procedure successfully completed.
--此时CF的值由原来的2k+增大到4w+，呈数量级变化
SQL> select T.TABLE_NAME,
2 T.BLOCKS,
3 T.NUM_ROWS,
4 T1.INDEX_NAME,
5 T1.LEAF_BLOCKS,
6 T1.CLUSTERING_FACTOR
7 from dba_tables t, dba_indexes t1
8 where t.TABLE_NAME = t1.table_name
9 and t.table_name = 'T3';
--此时CF的值由原来的2k+增大到4w+，呈数量级变化
SQL> select T.TABLE_NAME,
2 T.BLOCKS,
3 T.NUM_ROWS,
4 T1.INDEX_NAME,
5 T1.LEAF_BLOCKS,
6 T1.CLUSTERING_FACTOR
7 from dba_tables t, dba_indexes t1
8 where t.TABLE_NAME = t1.table_name
9 and t.table_name = 'T3';
TABL BLOCKS NUM_ROWS INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR
---- ---------- ---------- -------------------- ----------- -----------------
T3 2875 1000000 PK_T3_ID 1875 42691
---- ---------- ---------- -------------------- ----------- -----------------
T3 2875 1000000 PK_T3_ID 1875 42691
c、重建t3
-->下面通过重建t3来缩小CF的值，新的表名为t4
SQL> drop table t4 purge; --->删除之前的临时表
-->下面通过重建t3来缩小CF的值，新的表名为t4
SQL> drop table t4 purge; --->删除之前的临时表
Table dropped.
SQL> create table t4 nologging as select * from t3 order by id;
SQL> create table t4 nologging as select * from t3 order by id;
Table created.
SQL> alter table t4 add constraint pk_t4_id primary key(id);
SQL> alter table t4 add constraint pk_t4_id primary key(id);
Table altered.
SQL> exec dbms_stats.gather_table_stats('sys','T4',cascade=>true);
SQL> exec dbms_stats.gather_table_stats('sys','T4',cascade=>true);
PL/SQL procedure successfully completed.
--->表t4上的CF值(2883)小于原始的CF值(2874)
SQL> select T.TABLE_NAME,
2 T.BLOCKS,
3 T.NUM_ROWS,
4 T1.INDEX_NAME,
5 T1.LEAF_BLOCKS,
6 T1.CLUSTERING_FACTOR
7 from dba_tables t, dba_indexes t1
8 where t.TABLE_NAME = t1.table_name
9 and t.table_name = 'T4';
--->表t4上的CF值(2883)小于原始的CF值(2874)
SQL> select T.TABLE_NAME,
2 T.BLOCKS,
3 T.NUM_ROWS,
4 T1.INDEX_NAME,
5 T1.LEAF_BLOCKS,
6 T1.CLUSTERING_FACTOR
7 from dba_tables t, dba_indexes t1
8 where t.TABLE_NAME = t1.table_name
9 and t.table_name = 'T4';
TABL BLOCKS NUM_ROWS INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR
---- ---------- ---------- -------------------- ----------- -----------------
T4 2883 1000000 PK_T4_ID 2088 2883
d、比较不同的CF对查询性能的影响
-->下面来基于表t3与t4来比较一下不同的CF对查询的影响
SQL> set autotrace trace
SQL> select * from t3 where id between 10000 and 15000;
---- ---------- ---------- -------------------- ----------- -----------------
T4 2883 1000000 PK_T4_ID 2088 2883
d、比较不同的CF对查询性能的影响
-->下面来基于表t3与t4来比较一下不同的CF对查询的影响
SQL> set autotrace trace
SQL> select * from t3 where id between 10000 and 15000;
5001 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1434024551
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5002 | 80032 | 227 (0)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 5002 | 80032 | 227 (0)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | PK_T3_ID | 5002 | | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5002 | 80032 | 227 (0)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 5002 | 80032 | 227 (0)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | PK_T3_ID | 5002 | | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
2 - access("ID">=10000 AND "ID"<=15000)
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
2028 consistent gets
12 physical reads
0 redo size
162538 bytes sent via SQL*Net to client
4183 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5001 rows processed
--原始表上的查询的cost为03, consistent gets与physical reads分别为2028,12
SQL> select * from t4 where id between 10000 and 15000;
5001 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 625722358
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5002 | 80032 | 28 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T4 | 5002 | 80032 | 28 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_T4_ID | 5002 | | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5002 | 80032 | 28 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T4 | 5002 | 80032 | 28 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_T4_ID | 5002 | | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
2 - access("ID">=10000 AND "ID"<=15000)
Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
718 consistent gets
0 physical reads
0 redo size
162538 bytes sent via SQL*Net to client
4183 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
5001 rows processed
--新创建的表的cost 为01, consistent gets与physical reads分别为718,0. 低于原表的开销
-->可以将原始表t3上的数据删除(truncate)，删除前禁用依赖于该表的所有约束，然后将t4的数据使用order by插入到t3
SQL> truncate table t3;
Table truncated.
SQL> insert into t3 select * from t4 order by id;
1000000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('sys','T3',cascade=>true);
PL/SQL procedure successfully completed.
--CF值由后来的42691重新降为2874
SQL> select T.TABLE_NAME,
2 T.BLOCKS,
3 T.NUM_ROWS,
4 T1.INDEX_NAME,
5 T1.LEAF_BLOCKS,
6 T1.CLUSTERING_FACTOR
7 from dba_tables t, dba_indexes t1
8 where t.TABLE_NAME = t1.table_name
9 and t.table_name = 'T3';
--CF值由后来的42691重新降为2874
SQL> select T.TABLE_NAME,
2 T.BLOCKS,
3 T.NUM_ROWS,
4 T1.INDEX_NAME,
5 T1.LEAF_BLOCKS,
6 T1.CLUSTERING_FACTOR
7 from dba_tables t, dba_indexes t1
8 where t.TABLE_NAME = t1.table_name
9 and t.table_name = 'T3';
TABL BLOCKS NUM_ROWS INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR
---- ---------- ---------- -------------------- ----------- -----------------
T3 2875 1000000 PK_T3_ID 1875 2874
-->注上面的create table as ..方式并不适合用于生产环境的真实操作，因为表上的一些属性会被忽略掉.
---- ---------- ---------- -------------------- ----------- -----------------
T3 2875 1000000 PK_T3_ID 1875 2874
-->注上面的create table as ..方式并不适合用于生产环境的真实操作，因为表上的一些属性会被忽略掉.
9、小结
a、任意情形下(堆表)，表上数据的存储只能按照一种特定的顺序进行存储。
b、由上面的特性决定了表上的只有一个特定的索引列(单索引或组合索引)具有最佳的CF值。
c、索引的创建应考虑按应该按照经常频繁读取的大范围数据的读取顺序来创建索引，以保证得到最佳的CF值。
d、索引在被创建之时，基于该索引列上的CF值即被产生，但表上的DML操作后需要收集统计信息才可以更新CF的值。
e、基于表上频繁的DML操作，尤其是delete后再新增记录，可用空闲空间被填充，将使得CF的值呈增大趋势。
f、alter table move tabname并不会影响CF的值，该功能只是移动高水位线，且不释放空间。
g、重建索引对CF的值收效甚微，因为原始表数据存储顺序未发生根本变化。
h、CF的值是影响查询分析器对执行计划的评估与生成的因素之一(即是否走索引还是全表扫描，嵌套连接时哪个表为驱动表等)。
i、通过重建表或使用聚簇表来改进CF的值，建议将原始表数据填充到临时表，禁用依赖于该表的所有约束后truncate该表，再从临时表导回数据(按顺序)，启用约束。
a、任意情形下(堆表)，表上数据的存储只能按照一种特定的顺序进行存储。
b、由上面的特性决定了表上的只有一个特定的索引列(单索引或组合索引)具有最佳的CF值。
c、索引的创建应考虑按应该按照经常频繁读取的大范围数据的读取顺序来创建索引，以保证得到最佳的CF值。
d、索引在被创建之时，基于该索引列上的CF值即被产生，但表上的DML操作后需要收集统计信息才可以更新CF的值。
e、基于表上频繁的DML操作，尤其是delete后再新增记录，可用空闲空间被填充，将使得CF的值呈增大趋势。
f、alter table move tabname并不会影响CF的值，该功能只是移动高水位线，且不释放空间。
g、重建索引对CF的值收效甚微，因为原始表数据存储顺序未发生根本变化。
h、CF的值是影响查询分析器对执行计划的评估与生成的因素之一(即是否走索引还是全表扫描，嵌套连接时哪个表为驱动表等)。
i、通过重建表或使用聚簇表来改进CF的值，建议将原始表数据填充到临时表，禁用依赖于该表的所有约束后truncate该表，再从临时表导回数据(按顺序)，启用约束。
文章内容转载：https://blog.****.net/leshami/article/details/8847959