首页 » ORACLE [C]系列, ORACLE 9i-23c » Oracle 12C新特性: Attribute Clustering

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 —

打赏

对不起,这篇文章暂时关闭评论。