oracle 聚簇因子
聚簇因子是Oracle统计信息中在CBO优化器模式下用于计算cost的参数之一，决定了当前的SQL语句是否走索引，还是全表扫描以及是否作为嵌套连接外部表等。
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、影响聚簇因子的情形
当插入到表的数据与索引的顺序相同时，可以提高聚簇因子(接近表上的块数)。
因此，任意影响该顺序的情形都将导致索引列上的聚簇因子变差。
如列的顺序，反向索引，空闲列表或空闲列表组。
5、提高聚簇因子
堆表的数据存储是无序存储，因此需要使无序变为有序。下面是提高聚簇因子的办法。
a、对于表上的多个索引以及组合索引的情形，索引的创建应考虑按应该按照经常频繁读取的大范围数据的读取顺序来创建索引。
b、定期重构表(针对堆表)，也就是使得表与索引上的数据顺序更接近。注意，是重构表，而不是重建索引。
重建索引并不能显剧提高CF的值，因为索引列通常是有序的，无序的是原始表上的数据。
提取原始表上的数据到一个临时表，禁用依赖于该表的相关约束，truncate原始表，再将临时表的数据按索引访问顺序填充到原始表。
c、使用聚簇表来代替堆表。
6、实验模拟聚簇因子的情形：
6.1、基于列顺序对CF的影响
MZF@mzf1> create table test as select * from dba_objects order by object_name;
Table created.
MZF@mzf1> create index i_obj_name on test(object_name);
Index created.
MZF@mzf1> create index i_obj_id on test(object_id);
Index created.
MZF@mzf1> exec dbms_stats.gather_table_stats('mzf','TEST',cascade=>true);
PL/SQL procedure successfully completed.
MZF@mzf1> @idx_stat
Session altered.
Enter value for input_table_name: TEST
old 6: and a.table_name='&input_table_name'
new 6: and a.table_name='TEST'
Enter value for owner_name: MZF
old 7: and a.owner='&owner_name'
new 7: and a.owner='MZF'
BLEVEL INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS L_PER_KEY D_PER_KEY CLUSTERING_FACTOR LAST_ANALYZED BLOCKS NUM_ROWS
---------- -------------------- ----------- ------------- ---------- ---------- ----------------- ------------------- ---------- ----------
2 I_OBJ_NAME 458 53764 1 1 1537 2016-10-08 10:23:27 1571 91616
1 I_OBJ_ID 203 91616 1 1 73899 2016-10-08 10:23:27 1571 91616
--从上面的查询可以看出，索引I_OBJ_NAME的聚簇因子小于表上的块数，一个良好的CF值，因为object_name列是有序插入的。
--而索引I_OBJ_ID上的CF接近整个表中的行数，说明该索引上的CF值不是很理想，因为object_id在插入到table时是无序的。
--从上可知，一个表只能有一种有序的方式来组织数据。因此对于多出一个索引的表，且顺序按照非插入时的顺序时，则其他索引上的聚簇因子很难获得理想的值。
6.2、组合索引对CF的影响
MZF@mzf1> create index i_obj_name_id on test(object_name, object_id);
Index created.
MZF@mzf1> create index i_obj_id_name on test(object_id, object_name);
Index created.
MZF@mzf1> exec dbms_stats.gather_table_stats('mzf','TEST',cascade=>true);
PL/SQL procedure successfully completed.
MZF@mzf1> @idx_stat
Session altered.
Enter value for input_table_name: TEST
old 6: and a.table_name='&input_table_name'
new 6: and a.table_name='TEST'
Enter value for owner_name: MZF
old 7: and a.owner='&owner_name'
new 7: and a.owner='MZF'
BLEVEL INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS L_PER_KEY D_PER_KEY CLUSTERING_FACTOR LAST_ANALYZED BLOCKS NUM_ROWS
---------- -------------------- ----------- ------------- ---------- ---------- ----------------- ------------------- ---------- ----------
2 I_OBJ_NAME 458 53764 1 1 1537 2016-10-08 10:55:24 1571 91616
1 I_OBJ_ID 203 91616 1 1 73899 2016-10-08 10:55:24 1571 91616
2 I_OBJ_NAME_ID 520 91616 1 1 2119 2016-10-08 10:55:24 1571 91616
1 I_OBJ_ID_NAME 520 91616 1 1 73899 2016-10-08 10:55:24 1571 91616
--从上面的结果可知：
--新创建的组合索引，I_OBJ_NAME_ID(object_name, object_id),object_name是前导列，因此CF值尽管比单列是大，依然表现良好。
--而索引I_OBJ_ID_NAME(object_id, object_name)，object_id作为前导列，CF值与单列索引I_OBJ_ID相同。
--上面的四个索引来看，无论是单列还是符合索引，当索引列(leaf)的顺序接近于表上行的顺序，CF表现良好。
6.3、反向索引对CF的影响(反转索引主要是重新分布索引值，也就是将相连比较紧密地索引键值分散到不同或相距比较远的快上以避免竞争)
MZF@mzf1> create table test2 nologging as select * from test;
Table created.
MZF@mzf1> create index i_obj_name_reverse on test2(object_name) reverse;
Index created.
MZF@mzf1> exec dbms_stats.gather_table_stats('mzf','TEST2',cascade=>true);
PL/SQL procedure successfully completed.
MZF@mzf1> @idx_stat
Session altered.
Enter value for input_table_name: TEST2
old 6: and a.table_name='&input_table_name'
new 6: and a.table_name='TEST2'
Enter value for owner_name: MZF
old 7: and a.owner='&owner_name'
new 7: and a.owner='MZF'
BLEVEL INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS L_PER_KEY D_PER_KEY CLUSTERING_FACTOR LAST_ANALYZED BLOCKS NUM_ROWS
---------- -------------------- ----------- ------------- ---------- ---------- ----------------- ------------------- ---------- ----------
2 I_OBJ_NAME_REVERSE 458 53764 1 1 52872 2016-10-08 11:00:30 1571 91616
--上面创建的反向索引的CF较之前的都要大，因索引键上的值是反向的，也就是说是无序的。
--在段空间管理基于手动管理的方式下，如果使用freelist可以避免段操作上DML的竞争，但索引列上将具有较比较糟糕的聚簇因子(演示省略)
6.4、DML对CF的影响
--创建一张存有100W条记录数的表
MZF@mzf1> @a
Table created.
Sequence dropped.
Sequence created.
PL/SQL procedure successfully completed.
MZF@mzf1> select count(*) from big_table;
COUNT(*)
----------
1000000
MZF@mzf1> @idx_stat
Session altered.
Enter value for input_table_name: BIG_TABLE
old 6: and a.table_name='&input_table_name'
new 6: and a.table_name='BIG_TABLE'
Enter value for owner_name: MZF
old 7: and a.owner='&owner_name'
new 7: and a.owner='MZF'
BLEVEL INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS L_PER_KEY D_PER_KEY CLUSTERING_FACTOR LAST_ANALYZED BLOCKS NUM_ROWS
---------- -------------------- ----------- ------------- ---------- ---------- ----------------- ------------------- ---------- ----------
2 SYS_C0010320 1875 1000000 1 1 1788 2016-10-08 11:59:45 1882 1000000
--创建一张临时表来存储即将要做delete的数据
MZF@mzf1> create table big_table_tmp nologging as select * from big_table where id>=10000 and id<=200000;
Table created.
MZF@mzf1> delete from big_table nologging where id>=10000 and id<=200000;
190001 rows deleted.
MZF@mzf1> commit;
Commit complete.
MZF@mzf1> @idx_stat
Session altered.
Enter value for input_table_name: BIG_TABLE
old 6: and a.table_name='&input_table_name'
new 6: and a.table_name='BIG_TABLE'
Enter value for owner_name: MZF
old 7: and a.owner='&owner_name'
new 7: and a.owner='MZF'
BLEVEL INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS L_PER_KEY D_PER_KEY CLUSTERING_FACTOR LAST_ANALYZED BLOCKS NUM_ROWS
---------- -------------------- ----------- ------------- ---------- ---------- ----------------- ------------------- ---------- ----------
2 SYS_C0010320 1875 1000000 1 1 1788 2016-10-08 11:59:45 1882 1000000
--从上面的查询可知，删除记录并不影响CF。
--收集统计信息后，发现CF下降了，但是BLOCKS没有变化，因为你只是删除了记录数，并没有释放空间。
MZF@mzf1> exec dbms_stats.gather_table_stats('mzf','BIG_TABLE',cascade=>true);
PL/SQL procedure successfully completed.
MZF@mzf1> @idx_stat
Session altered.
Enter value for input_table_name: BIG_TABLE
old 6: and a.table_name='&input_table_name'
new 6: and a.table_name='BIG_TABLE'
Enter value for owner_name: MZF
old 7: and a.owner='&owner_name'
new 7: and a.owner='MZF'
BLEVEL INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS L_PER_KEY D_PER_KEY CLUSTERING_FACTOR LAST_ANALYZED BLOCKS NUM_ROWS
---------- -------------------- ----------- ------------- ---------- ---------- ----------------- ------------------- ---------- ----------
2 SYS_C0010320 1520 809999 1 1 1449 2016-10-08 12:01:08 1882 809999
--将删除数据重新插入到表中，按照一定的顺序
MZF@mzf1> insert into big_table nologging select * from big_table_tmp where id>=150000 and id<=200000 order by id;
50001 rows created.
MZF@mzf1> insert into big_table nologging select * from big_table_tmp where id>=10000 and id<150000 order by id;
140000 rows created.
MZF@mzf1> commit;
Commit complete.
MZF@mzf1> @idx_stat
Session altered.
Enter value for input_table_name: BIG_TABLE
old 6: and a.table_name='&input_table_name'
new 6: and a.table_name='BIG_TABLE'
Enter value for owner_name: MZF
old 7: and a.owner='&owner_name'
new 7: and a.owner='MZF'
BLEVEL INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS L_PER_KEY D_PER_KEY CLUSTERING_FACTOR LAST_ANALYZED BLOCKS NUM_ROWS
---------- -------------------- ----------- ------------- ---------- ---------- ----------------- ------------------- ---------- ----------
2 SYS_C0010320 1520 809999 1 1 1449 2016-10-08 12:01:08 1882 809999
MZF@mzf1> exec dbms_stats.gather_table_stats('mzf','BIG_TABLE',cascade=>true);
PL/SQL procedure successfully completed.
MZF@mzf1> @idx_stat
Session altered.
Enter value for input_table_name: BIG_TABLE
old 6: and a.table_name='&input_table_name'
new 6: and a.table_name='BIG_TABLE'
Enter value for owner_name: MZF
old 7: and a.owner='&owner_name'
new 7: and a.owner='MZF'
BLEVEL INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS L_PER_KEY D_PER_KEY CLUSTERING_FACTOR LAST_ANALYZED BLOCKS NUM_ROWS
---------- -------------------- ----------- ------------- ---------- ---------- ----------------- ------------------- ---------- ----------
2 SYS_C0010320 1875 1000000 1 1 1792 2016-10-08 12:37:27 1882 1000000
--查询结果显示，在执行插入语句后，CF从原来的1449变化到了1792，CF增大了。由于本次实验的表结构较为简单，一些明显的效果不是很清楚。正常情况下，CF是呈量级
别的增加，因为在插入数据的时候它会根据每个块空闲度去插入，而不是进行有序的插入。
--move方式对CF的影响
MZF@mzf1> alter table big_table move;
Table altered.
MZF@mzf1> @idx_stat
Session altered.
Enter value for input_table_name: BIG_TABLE
old 6: and a.table_name='&input_table_name'
new 6: and a.table_name='BIG_TABLE'
Enter value for owner_name: MZF
old 7: and a.owner='&owner_name'
new 7: and a.owner='MZF'
BLEVEL INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS L_PER_KEY D_PER_KEY CLUSTERING_FACTOR LAST_ANALYZED BLOCKS NUM_ROWS
---------- -------------------- ----------- ------------- ---------- ---------- ----------------- ------------------- ---------- ----------
2 SYS_C0010320 1875 1000000 1 1 1792 2016-10-08 12:37:27 1882 1000000
MZF@mzf1> exec dbms_stats.gather_table_stats('mzf','BIG_TABLE',cascade=>true);
BEGIN dbms_stats.gather_table_stats('mzf','BIG_TABLE',cascade=>true); END;
*
ERROR at line 1:
ORA-20000: index "MZF"."BIG_TABLE_PK" or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 13182
ORA-06512: at "SYS.DBMS_STATS", line 13202
ORA-06512: at line 1
MZF@mzf1> alter index SYS_C0010320 rebuild nologging;
Index altered.
MZF@mzf1> exec dbms_stats.gather_table_stats('mzf','BIG_TABLE',cascade=>true);
PL/SQL procedure successfully completed.
MZF@mzf1> @idx_stat
Session altered.
Enter value for input_table_name: BIG_TABLE
old 6: and a.table_name='&input_table_name'
new 6: and a.table_name='BIG_TABLE'
Enter value for owner_name: MZF
old 7: and a.owner='&owner_name'
new 7: and a.owner='MZF'
BLEVEL INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS L_PER_KEY D_PER_KEY CLUSTERING_FACTOR LAST_ANALYZED BLOCKS NUM_ROWS
---------- -------------------- ----------- ------------- ---------- ---------- ----------------- ------------------- ---------- ----------
2 SYS_C0010320 2088 1000000 1 1 3568 2016-10-08 12:40:25 1836 1000000
--查询结果显示，在重建索引之后，CF的值变大了。
6.5、比较CF不同对查询性能的影响
MZF@mzf1> drop table big_table_tmp purge;
Table dropped.
MZF@mzf1> create table big_table_tmp nologging as select * from big_table order by id;
Table created.
MZF@mzf1> create unique index big_table_tmp_pk on big_table_tmp(id);
Index created.
MZF@mzf1> alter table big_table_tmp add constraint big_table_tmp_pk primary key(id) using index big_table_tmp_pk;
Table altered.
MZF@mzf1> exec dbms_stats.gather_table_stats('mzf','BIG_TABLE_TMP',cascade=>true);
PL/SQL procedure successfully completed.
MZF@mzf1> @idx_stat
Session altered.
Enter value for input_table_name: BIG_TABLE_TMP
old 6: and a.table_name='&input_table_name'
new 6: and a.table_name='BIG_TABLE_TMP'
Enter value for owner_name: MZF
old 7: and a.owner='&owner_name'
new 7: and a.owner='MZF'
BLEVEL INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS L_PER_KEY D_PER_KEY CLUSTERING_FACTOR LAST_ANALYZED BLOCKS NUM_ROWS
---------- -------------------- ----------- ------------- ---------- ---------- ----------------- ------------------- ---------- ----------
2 BIG_TABLE_TMP_PK 2088 1000000 1 1 1798 2016-10-08 12:43:35 1836 1000000
MZF@mzf1> set autot trace;
MZF@mzf1> select * from big_table where id between 10000 and 15000;
5001 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4017324332
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5002 | 40016 | 31 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BIG_TABLE | 5002 | 40016 | 31 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_C0010320 | 5002 | | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=10000 AND "ID"<=15000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
698 consistent gets
0 physical reads
0 redo size
130211 bytes sent via SQL*Net to client
4215 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5001 rows processed
MZF@mzf1> select * from big_table_tmp where id between 10000 and 15000;
5001 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2831592934
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5002 | 40016 | 22 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BIG_TABLE_TMP | 5002 | 40016 | 22 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | BIG_TABLE_TMP_PK | 5002 | | 13 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=10000 AND "ID"<=15000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
689 consistent gets
0 physical reads
0 redo size
130211 bytes sent via SQL*Net to client
4215 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5001 rows processed
--查询结果显示，CF的大小直接影响着查询性能。
-->可以将原始表big_table上的数据删除(truncate)，删除前禁用依赖于该表的所有约束，然后将big_table_tmp的数据使用order by插入到big_table
-->上面的create table as ..方式并不适合用于生产环境的真实操作，因为表上的一些属性会被忽略掉.
7、小结
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该表，再从临时表导回数据(按顺序)，启用约束。
j、不推荐使用create table as select(CTAS)，因为表上的一些特性会被忽略，可以用SQL*Plus copy方式来实现。具体参考: 当心 CREATE TABLE AS
精简脚本：
idx_stat.sql
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set linesize 300;
set pagesize 50;
col index_name for a20;
select a.BLEVEL,a.INDEX_NAME,a.LEAF_BLOCKS,a.DISTINCT_KEYS,a.AVG_LEAF_BLOCKS_PER_KEY l_per_key,
a.AVG_DATA_BLOCKS_PER_KEY d_per_key, a.CLUSTERING_FACTOR,
a.LAST_ANALYZED,b.BLOCKS,b.NUM_ROWS
from dba_indexes a, dba_tables b
where a.table_name=b.table_name
and a.table_name='&input_table_name'
and a.owner='&owner_name';
create table big_table(id number primary key,name varchar2(10));
create sequence mzf start with 1 increment by 1 nomaxvalue nocycle;
declare
v_id number;
v_random number;
begin
v_id :=1;
v_random :=1;
for i in 1 .. 1000000 loop
while 1 = 1 loop
select mzf.nextval into v_id from dual;
exit when v_id <> v_random;
end loop;
v_random := v_id;
insert into "MZF"."BIG_TABLE"
(id,
name)
values
(v_id,
'aa');
if mod(i,500) = 0 then
commit;
end if;
end loop;
commit;
end;
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30192548/viewspace-2125929/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
oracle logmnr使用
请登录后发表评论
登录
全部评论
