Asynchronous (Delayed) Global Index Maintenance for Drop and Truncate Partition

Setup 

The following code creates and populates a partitioned table with global indexes.

[root@oracle-db-19c ~]# 
[root@oracle-db-19c ~]# su - oracle
[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 14 10:57:22 2023
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show user
USER is "SYS"
SQL> 
SQL> 
SQL> conn maxwellpan/maxwellpan@PDB1;
Connected.
SQL> 
SQL> DROP TABLE t1 PURGE;

Table dropped.

SQL> set pagesize 200 linesize 200
SQL> 
SQL> CREATE TABLE t1
  2  (id            NUMBER,
  3   description   VARCHAR2(50),
  4   created_date  DATE)
  5  PARTITION BY RANGE (created_date)
  6  (PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users,
  7   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> -- Populate it so segments are created.
SQL> 
SQL> INSERT /*+ APPEND */ INTO t1
  2  SELECT level,
       'Description for ' || level,
  4         CASE
  5           WHEN MOD(level,2) = 0 THEN TO_DATE('01/07/2014', 'DD/MM/YYYY')
  6           ELSE TO_DATE('01/07/2015', 'DD/MM/YYYY')
  7         END
  8  FROM   dual
  9  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.

SQL> 
SQL> -- Check the indexes.
SQL> COLUMN table_name FORMAT A20
SQL> COLUMN index_name FORMAT A20
SQL> SElECT table_name,
  2         index_name,
  3         status
  4  FROM   user_indexes
  5  ORDER BY 1,2;

TABLE_NAME           INDEX_NAME           STATUS
-------------------- -------------------- ------------------------
NORM_TAB             IDX_NOR_TAB_COL2     VALID
NORM_TAB             IDX_NOR_TAB_COL3     VALID
PART_TAB             IDX_PAR_TAB_COL2     N/A
PART_TAB             IDX_PAR_TAB_COL3     VALID
SPM_TEST_TAB         SPM_TEST_TAB_IDX     VALID
T                    IDX1_OBJECT_ID       VALID
T                    IDX_UN_OBJID_OBJNAME VALID
T1                   T1_IDX               VALID
T1                   T1_PK                VALID
T3                   IDX_ID_T3            VALID
T4                   IDX_ID_T4            VALID
TAB1                 TAB1_CODE            VALID
TAB1                 TAB1_PK              VALID
TAB2                 TAB2_PK              VALID
TAB2                 TAB2_TAB1_FKI        VALID
T_COLOCATED          PK_T_COLOCATED       VALID
T_DISORGANIZED       PK_T_DISORG          VALID
T_MAX                IDX_T_MAX_OBJ        VALID

18 rows selected.

SQL> 

Asynchronous Global Index Maintenance

When combined with the UPDATE INDEXES clause the DROP PARTITION and TRUNCATE PARTITION commands now result in metadata-only index maintenance. This functionality is only available for heap tables and is not supported on tables with object types, domain indexes or those owned by SYS.

The actual index maintenance is performed at a later time, triggered by one of the following.

• The SYS.PMO_DEFERRED_GIDX_MAINT_JOB job is scheduled to run at 02:00 every day.
• The SYS.PMO_DEFERRED_GIDX_MAINT_JOB job can be run manually using the DBMS_SCHEDULER.RUN_JOB procedure.
• Run the DBMS_PART.CLEANUP_GIDX procedure.
• Run the ALTER INDEX REBUILD [PARTITION] command.
• Run the ALTER INDEX [PARTITION] COALESCE CLEANUP command.

If we just dropped or truncated a partition, the global indexes would be marked as invalid. Prior to 12c, using the UPDATE INDEXES clause would cause them to be rebuilt as part of the operation, making the whole operation slower. In the following example we truncate a partition and check the status of the indexes.

SQL> 
SQL> -- Truncate a partition.
SQL> 
SQL> ALTER TABLE t1 TRUNCATE PARTITION part_2014 DROP STORAGE UPDATE INDEXES;

Table truncated.

SQL> -- ALTER TABLE t1 DROP PARTITION part_2014 UPDATE INDEXES;
SQL> 
SQL> 
SQL> -- Check the status of the indexes.
SQL> SElECT table_name,
  2         index_name,
  3         status
FROM   user_indexes
  5  ORDER BY 1,2;

TABLE_NAME           INDEX_NAME           STATUS
-------------------- -------------------- ------------------------
NORM_TAB             IDX_NOR_TAB_COL2     VALID
NORM_TAB             IDX_NOR_TAB_COL3     VALID
PART_TAB             IDX_PAR_TAB_COL2     N/A
PART_TAB             IDX_PAR_TAB_COL3     VALID
SPM_TEST_TAB         SPM_TEST_TAB_IDX     VALID
T                    IDX1_OBJECT_ID       VALID
T                    IDX_UN_OBJID_OBJNAME VALID
T1                   T1_IDX               VALID
T1                   T1_PK                VALID
T3                   IDX_ID_T3            VALID
T4                   IDX_ID_T4            VALID
TAB1                 TAB1_CODE            VALID
TAB1                 TAB1_PK              VALID
TAB2                 TAB2_PK              VALID
TAB2                 TAB2_TAB1_FKI        VALID
T_COLOCATED          PK_T_COLOCATED       VALID
T_DISORGANIZED       PK_T_DISORG          VALID
T_MAX                IDX_T_MAX_OBJ        VALID

18 rows selected.

SQL> 

The new ORPHANED_ENTRIES column in the USER_INDEXES view shows the index maintenance has not been done yet.

QL> 
SQL> -- Check if index maintenance is needed.
SQL> SELECT index_name,
  2         orphaned_entries
  3  FROM   user_indexes
  4  ORDER BY 1;

INDEX_NAME           ORPHANED_
-------------------- ---------
IDX1_OBJECT_ID       NO
IDX_ID_T3            NO
IDX_ID_T4            NO
IDX_NOR_TAB_COL2     NO
IDX_NOR_TAB_COL3     NO
IDX_PAR_TAB_COL2     NO
IDX_PAR_TAB_COL3     NO
IDX_T_MAX_OBJ        NO
IDX_UN_OBJID_OBJNAME NO
PK_T_COLOCATED       NO
PK_T_DISORG          NO
SPM_TEST_TAB_IDX     NO
T1_IDX               YES
T1_PK                YES
TAB1_CODE            NO
TAB1_PK              NO
TAB2_PK              NO
TAB2_TAB1_FKI        NO

18 rows selected.

SQL> 

If we manually trigger the index maintenance, we can see the change reflected in the ORPHANED_ENTRIES column.

SQL> 
SQL> -- Manually trigger the index maintenance.
SQL> EXEC DBMS_PART.cleanup_gidx(USER, 't1');

PL/SQL procedure successfully completed.

SQL> -- Check if index maintenance is needed.
SQL> SELECT index_name,
  2         orphaned_entries
  3  FROM   user_indexes
  4  ORDER BY 1;

INDEX_NAME           ORPHANED_
-------------------- ---------
IDX1_OBJECT_ID       NO
IDX_ID_T3            NO
IDX_ID_T4            NO
IDX_NOR_TAB_COL2     NO
IDX_NOR_TAB_COL3     NO
IDX_PAR_TAB_COL2     NO
IDX_PAR_TAB_COL3     NO
IDX_T_MAX_OBJ        NO
IDX_UN_OBJID_OBJNAME NO
PK_T_COLOCATED       NO
PK_T_DISORG          NO
SPM_TEST_TAB_IDX     NO
T1_IDX               NO
T1_PK                NO
TAB1_CODE            NO
TAB1_PK              NO
TAB2_PK              NO
TAB2_TAB1_FKI        NO

18 rows selected.

SQL>