Oracle 12C新特性: Attribute Clustering
提起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
下面测试
[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 —
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。