Global index for performance optimization & feature enhancement in oracle12c asynchronous maintenance of drop and truncate partitions

In Oracle 12c, the performance of some drop and truncate partition commands can be optimized by delaying the maintenance of related indexes, while keeping the global index valid.

1. Settings

The following example shows the process of creating and loading data from a table with a global index.

--Build a table

CREATE TABLE T1

(ID number,

Comment VARCHAR2 (50),

Crt_time DATE)

PARTITION by RANGE (crt_time)

(PARTITION part_14 VALUES less THAN (to_date (' 01/01/2015 ', ' dd/mm/yyyy ') tablespace users,

PARTITION part_15 VALUES Less THAN (to_date (' 01/01/2016 ', ' dd/mm/yyyy ') tablespace users);

ALTER TABLE T1 ADD CONSTRAINT t1_pk PRIMARY KEY (ID);

CREATE INDEX t1_idx on T1 (crt_time);

--Load data

INSERT/*+ APPEND */into T1

SELECT level,

' Commit for ' | | Level

Case

When MOD (level,2) = 0 thento_date (' 01/07/2014 ', ' dd/mm/yyyy ')

ELSE to_date (' 01/07/2015 ', ' dd/mm/yyyy ')

END

From dual

CONNECT by Level <= 10000;

COMMIT;

EXEC dbms_stats.gather_table_stats (USER, ' T1 ');

--Check Index

COLUMN table_name FORMAT A20

COLUMN index_name FORMAT A20

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>

2. Global index Asynchronous maintenance

The drop and truncate partition commands are now used with the update_indexes only to cause changes in related metadata. This feature is currently available only for heap tables, objects that do not support object tables, domain indexes, or sys users.

The actual index maintenance is executed later when one of the following conditions is met.

• SYS. Pmo_deferred_gidx_maint_job jobs are dispatched at 2 points per day.
• Through Dbms_scheduler. Run_job when running sys.pmo_deferred_gidx_maint_job manually.
• Run Dbms_part. CLEANUP_GIDX process.
• When you run the Alter INDEX REBUILD [PARTITION] command.
• When you run the Alter INDEX [PARTITION] coalesce cleanup command.

Before oracle12c, if we drop or truncate a partition, it will cause the global index to fail, and the update_indexes clause will cause the index to rebuild during the operation, slowing down the whole operation. In the following example, we truncate a partition and then view the index status.

--Truncate a partition

ALTER TABLE T1 TRUNCATE partitionpart_2014 DROP STORAGE UPDATE INDEXES;

Or

ALTER TABLE T1 DROP PARTITION part_2014update INDEXES;

--View index status

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>

Orphaned_entries new column in view User_indexe the index is not maintained yet.

--Check if index maintenance is required

SELECT Index_name,

Orphaned_entries

From User_indexes

ORDER by 1;

Index_name ORP

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

T1_idx YES

T1_PK YES

sql> If we manually trigger index maintenance, we will see changes in the Orphaned_entries column

--Manual Trigger index maintenance

Execdbms_part.cleanup_gidx (USER, ' T1 ');

--See if index maintenance is required

SELECT Index_name,

Orphaned_entries

From User_indexes

ORDER by 1;

Index_name ORP

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

T1_idx NO

T1_PK NO

Sql>

Global index for performance optimization &amp; feature enhancement in oracle12c asynchronous maintenance of drop and truncate partitions