Partitioning -- 13d : TRUNCATE and DROP Partitions and Global Indexes

A TRUNCATE or DROP Partition makes Global Indexes on a Partitioned Table UNUSABLE.

You may be lucky if the target partition was empty, resulting in Oracle maintaining Global Indexes as valid.  However, the accepted rule is that you either (a) use the UPDATE INDEXES clause [resulting in the TRUNCATE or DROP taking longer to run, effectively locking the table partitions] OR  (b) do a REBUILD of the Indexes that become UNUSABLE after the TRUNCATE or DROP.

12c has introduced what it calls Asynchronous Global Index Maintenance.  With this feature present, the TRUNCATE or DROP runs much faster as a DDL without actually removing the target rows from the Global Indexes [but still requires the UPDATE INDEXES clause to be specified]

So, now in my 12.2 database I have these two Indexes on SALES_DATA :

SQL> select index_name, partitioned, status
  2  from user_indexes
  3  where table_name = 'SALES_DATA'
  4  order by 2,1
  5  /

INDEX_NAME                     PAR STATUS
------------------------------ --- --------
SALES_DATA_PK                  NO  VALID
SALES_DATA_LCL_NDX_1           YES N/A

SQL> 

I then TRUNCATE a non-empty Partition and check the Indexes

SQL> alter table sales_data truncate partition P_2015 update indexes;

Table truncated.

SQL>
SQL> select index_name, partitioned, status, orphaned_entries
  2  from user_indexes
  3  where table_name = 'SALES_DATA'
  4  order by 2,1
  5  /

INDEX_NAME                     PAR STATUS   ORP
------------------------------ --- -------- ---
SALES_DATA_PK                  NO  VALID    YES
SALES_DATA_LCL_NDX_1           YES N/A      NO

SQL> 

The ORPHANED_ENTRIES column indicates that SALES_DATA_PK is subject to Asynchronous Index Maintenance.

This is the job that will do the Index Maintenance at 2am  :

SQL> l
  1  select owner, job_name, last_start_date, next_run_Date
  2  from dba_scheduler_jobs
  3* where job_name = 'PMO_DEFERRED_GIDX_MAINT_JOB'
SQL> /

OWNER
---------------------------------------------------------------------------
JOB_NAME
---------------------------------------------------------------------------
LAST_START_DATE
---------------------------------------------------------------------------
NEXT_RUN_DATE
---------------------------------------------------------------------------
SYS
PMO_DEFERRED_GIDX_MAINT_JOB
20-MAR-19 10.18.51.215433 AM UTC
21-MAR-19 02.00.00.223589 AM UTC


SQL> !date
Wed Mar 20 20:05:24 SGT 2019

SQL> 

So, I could
(1) wait for the next run of the job OR
(2) manually trigger the job (which will scan the entire database for all indexes that require such maintenance) OR
(3) Execute  DBMS_PART.CLEANUP_GIDX  to initiate the maintenance for the specific index OR
(4) Execute an ALTER INDEX REBUILD to make the Index USABLE again.

SQL> execute dbms_part.cleanup_gidx('HEMANT','SALES_DATA');

PL/SQL procedure successfully completed.

SQL> select index_name, partitioned, status, orphaned_entries
  2  from user_indexes
  3  where table_name = 'SALES_DATA'
  4  order by 2,1
  5  /

INDEX_NAME                     PAR STATUS   ORP
------------------------------ --- -------- ---
SALES_DATA_PK                  NO  VALID    NO
SALES_DATA_LCL_NDX_1           YES N/A      NO

SQL> 

Note that the argument to CLEANUP_GIDX is the *Table Name*, not an Index Name.


Here I have demonstrated a TRUNCATE Partition, but the same method would be usable for a DROP Partition.