oracle 全局索引 需要维护吗,Oracle 12c 新特性 ---异步全局索引维护，用于删除和截断分区...

概念

https://www.cndba.cn/leo1990/article/2145

Global index maintenance is decoupled from the DROP and TRUNCATE partition maintenance operation without rendering a global index unusable. Index maintenance is done asynchronously and can be delayed to a later point-in-time.

全局索引维护与DROP和TRUNCATE分区维护操作分离，而不会使全局索引无法使用。索引维护是异步完成的，可以延迟到稍后的时间点。

Delaying the global index maintenance to off-peak times without impacting the index availability makes DROP and TRUNCATE partition and subpartition maintenance operations faster and less resource intensive at the point-in-time of the partition maintenance operation.

在不影响索引可用性的情况下，将全局索引维护延迟到非高峰时间，在分区维护操作的时间点上降低和截断分区和子分区维护操作的速度更快，资源更少。

当与更新索引子句相结合时，DROP分区和TRUNCATE分区命令将导致元数据索引维护。此功能仅用于堆表，不支持对象类型、域索引或由SYS拥有的表。

实际的索引维护是在稍后的时间执行的，由以下之一触发。

SYS.PMO_DEFERRED_GIDX_MAINT_JOB作业预定在每天02:00运行。

SYS.PMO_DEFERRED_GIDX_MAINT_JOB作业手动运行DBMS_SCHEDULER.RUN_JOB存储过程。

运行DBMS_PART.CLEANUP_GIDX存储过程。

运行ALTER INDEX REBUILD [PARTITION]]命令。

运行ALTER INDEX [PARTITION] COALESCE CLEANUP命令。

详细内容可参考官方文档：

http://docs.oracle.com/database/121/VLDBG/GUID-087B87A6-959A-40C6-82AF-36E401FD089B.htm#VLDBG14107

https://www.cndba.cn/leo1990/article/2145

https://www.cndba.cn/leo1990/article/2145

1) 创建带有全局索引的分区表。

[leo@www.cndba.cn ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 17 23:30:39 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter session set container=pdbcndba;

Session altered.

SQL> conn test/test@pdbcndba

Connected.

SQL> DROP TABLE t1 PURGE;

Table dropped.

SQL> CREATE TABLE t1

(id NUMBER,

description VARCHAR2(50),

created_date DATE)

PARTITION BY RANGE (created_date)

(PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users,

PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users);

Table created.

SQL> ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (id);

Table altered.

SQL> CREATE INDEX t1_idx ON t1 (created_date);

Index created.

SQL> INSERT /*+ APPEND */ INTO t1

SELECT level,

'Description for ' || level,

CASE

WHEN MOD(level,2) = 0 THEN TO_DATE('01/07/2014', 'DD/MM/YYYY')

ELSE TO_DATE('01/07/2015', 'DD/MM/YYYY')

END

FROM dual

CONNECT BY level <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 't1');

PL/SQL procedure successfully completed.

2) 查看索引状态为VALID

SQL> COLUMN table_name FORMAT A20

SQL> COLUMN index_name FORMAT A20

SQL> SElECT table_name,

index_name,

status

FROM user_indexes

ORDER BY 1,2;

TABLE_NAME INDEX_NAME STATUS

-------------------- -------------------- --------

T1 T1_IDX VALID

T1 T1_PK VALID

3)如果我们只是删除或截断了一个分区，那么全局索引将被标记为无效。在12c之前，使用UPDATE INDEXES子句可以作为操作的一部分重新构建，使整个操作更慢。在下面的例子中，我们截断了一个分区并检查了索引的状态。

--truncate 分区

SQL> ALTER TABLE t1 TRUNCATE PARTITION part_2014 DROP STORAGE UPDATE INDEXES;

Table truncated.

4) 查看索引状态有效，USER_INDEXES视图中的ORPHANED_ENTRIES列显示索引维护还没有完成。

SQL> SElECT table_name,

index_name,

status

FROM user_indexes

ORDER BY 1,2;

TABLE_NAME INDEX_NAME STATUS

-------------------- -------------------- --------

T1 T1_IDX VALID

T1 T1_PK VALID

SQL> SELECT index_name,

orphaned_entries

FROM user_indexes

ORDER BY 1; 2 3 4

INDEX_NAME ORP

-------------------- ---

T1_IDX YES

T1_PK YES

5) 如果我们手动触发索引维护，就可以看到ORPHANED_ENTRIES列中反映的变化。

SQL> EXEC DBMS_PART.cleanup_gidx(USER, 't1');

PL/SQL procedure successfully completed.

SQL> SELECT index_name,

orphaned_entries

FROM user_indexes

ORDER BY 1; 2 3 4

INDEX_NAME ORP

-------------------- ---

T1_IDX NO

T1_PK NO

https://www.cndba.cn/leo1990/article/2145

参考文档https://www.cndba.cn/leo1990/article/2145

版权声明：本文为博主原创文章，未经博主允许不得转载。