提起index的cluster factor集群因子可能都并不陌生，反映了表上数据索引列顺序分散的程度，Attribute Clustering这是Oracle数据库版本12.1.0.2中的一项新功能，该功能允许dba在将表记录在insert写到磁盘时能否保持顺序，保持较好的cluster factor，从而使磁盘上的近按照批定列的顺序在物理记录保持紧密在一起。通过将具有相似值的记录聚类在一起，匹配特定sql过滤条件的数据将存储在磁盘上的同一块或相邻块上的可能性就更高。通过这种数据放置，与以插入顺序存储数据相比，可以用更少的磁盘IO操作检索请求的数据，所以调整物理顺序以匹配它们是有利的。但是它有一些限制，这里做几个小测试。
Attribute clustering在传统的DML中并不适用，仅在以下场景中实用:
- 1, CTAS
- 2, Bulk loads using direct path insert like : insert /*+ append */ select … from table
- 3, Data movement operations like:
- Alter table xx move [online]
- move Online table redefinition
测试环境19.3
[oracle@oel7db1 ~]$ sqlplus anbob/anbob@cdb1pdb1
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 22 00:42:30 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Fri Jan 22 2021 00:40:09 -05:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
USERNAME INST_NAME HOST_NAME I# SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
ANBOB PDB1-anbob19c oel7db1 1 75 55372 19.0.0.0.0 20210122 2527 56 2525 0000000078139938 0000000078D16AE8
SQL> create table t_ci(id number,name varchar2(30));
Table created.
SQL> insert into t_ci select trunc(dbms_random.value(1,9000000)),'anbob'||rownum from xmltable('1 to 100000');
100000 rows created.
SQL> commit;
Commit complete.
SQL> create index idx_t_ci_id on t_ci(id);
Index created.
SQL> @ind idx_t_ci
Display indexes where table or index name matches %idx_t_ci%...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
-------------------- ------------ ------------- ---- ------------------------------ ----
ANBOB T_CI IDX_T_CI_ID 1 ID
INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT
-------------------- ------------ ------------- ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
ANBOB T_CI IDX_T_CI_ID NORMAL NO VALID NO N 2 236 99472 100000 99650 2021-01-22 00:50:18 1 VISIBLE
Note:
传统insert 后index 的cluster factor 99650
SQL> create table t_ci_enable(id number,name varchar2(30))
CLUSTERING
BY LINEAR ORDER (ID)
YES ON LOAD YES ON DATA MOVEMENT;
SQL> insert into t_ci_enable select * from t_ci;
100000 rows created.
SQL> commit;
Commit complete.
SQL> create index idx_t_ci_enable_id on t_ci_enable(id);
Index created.
SQL> @ind idx_t_ci
Display indexes where table or index name matches %idx_t_ci%...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
ANBOB T_CI IDX_T_CI_ID 1 ID
T_CI_ENABLE IDX_T_CI_ENABLE_ID 1 ID
INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
ANBOB T_CI IDX_T_CI_ID NORMAL NO VALID NO N 2 236 98888 100000 99650 2021-01-22 01:00:30 1 VISIBLE
T_CI_ENABLE IDX_T_CI_ENABLE_ID NORMAL NO VALID NO N 2 236 99472 100000 99648 2021-01-22 01:08:45 1 VISIBLE
Note:
表级启用了CLUSTERING BY LINEAR ORDER， 传统insert和之前默认的cluster factor一个量级为 99648
SQL> create table t_ci_enable1(id number,name varchar2(30))
CLUSTERING
BY LINEAR ORDER (ID)
YES ON LOAD YES ON DATA MOVEMENT;
Table created.
SQL> insert /*+append*/ into t_ci_enable1 select * from t_ci;
100000 rows created.
SQL> create index idx_t_ci_enable1_id on t_ci_enable1(id);
Index created.
SQL> @ind idx_t_ci
Display indexes where table or index name matches %idx_t_ci%...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
ANBOB T_CI IDX_T_CI_ID 1 ID
T_CI_ENABLE IDX_T_CI_ENABLE_ID 1 ID
T_CI_ENABLE1 IDX_T_CI_ENABLE1_ID 1 ID
INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
ANBOB T_CI IDX_T_CI_ID NORMAL NO VALID NO N 2 236 98888 100000 99650 2021-01-22 01:00:30 1 VISIBLE
T_CI_ENABLE IDX_T_CI_ENABLE_ID NORMAL NO VALID NO N 2 236 99472 100000 99648 2021-01-22 01:08:45 1 VISIBLE
T_CI_ENABLE1 IDX_T_CI_ENABLE1_ID NORMAL NO VALID NO N 2 236 99472 100000 302 2021-01-22 01:12:00 1 VISIBLE
Note:
表级启用clustor order后，append 直接路径加载，索引的cluster factor为302, 当然CLUF越接近block 说明索引列数据越有序。
对比一下传统insert和insert append不同
SQL> explain plan for insert into t_ci_enable select * from t_ci;
Explained.
SQL> @x2
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3502766604
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 100K| 1660K| 103 (1)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | T_CI_ENABLE | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 100K| 1660K| 103 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T_CI | 100K| 1660K| 103 (1)| 00:00:01 |
------------------------------------------------------------------------------------------------
10 rows selected.
SQL> explain plan for insert /*+append*/ into t_ci_enable1 select * from t_ci;
Explained.
SQL> @x2
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3859407412
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 100K| 1660K| | 656 (1)| 00:00:01 |
| 1 | LOAD AS SELECT | T_CI_ENABLE1 | | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 100K| 1660K| | 656 (1)| 00:00:01 |
| 3 | SORT ORDER BY | | 100K| 1660K| 2760K| 656 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T_CI | 100K| 1660K| | 103 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
11 rows selected.
Note:
增加了sort order by .
对第一个已创建表维护增加Attribute Clustering属性。
SQL> alter table t_ci add clustering by linear order(id);
Table altered.
SQL> alter table t_ci move online;
Table altered.
SQL> @gts t_ci
Gather Table Statistics for table t_ci...
PL/SQL procedure successfully completed.
SQL> @ind t_ci
Display indexes where table or index name matches %t_ci%...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
ANBOB T_CI IDX_T_CI_ID 1 ID
T_CI_ENABLE IDX_T_CI_ENABLE_ID 1 ID
T_CI_ENABLE1 IDX_T_CI_ENABLE1_ID 1 ID
INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
ANBOB T_CI IDX_T_CI_ID NORMAL NO VALID NO N 2 236 98888 100000 302 2021-01-22 01:29:55 1 VISIBLE
T_CI_ENABLE IDX_T_CI_ENABLE_ID NORMAL NO VALID NO N 2 236 99472 100000 99648 2021-01-22 01:08:45 1 VISIBLE
T_CI_ENABLE1 IDX_T_CI_ENABLE1_ID NORMAL NO VALID NO N 2 236 99472 100000 302 2021-01-22 01:12:00 1 VISIBLE
Note:
可见启用该特性后，move重组后的数据，cluster factor 同样也只有302.
— enjoy —
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
