12c Database : Asynchronous index maintainence

Earlier to 12c, when an partition is truncated with update global index clause the operation will take longer to complete since it has to perform the operation on index also to remove corresponding keys in index partition/index and then rebuild entire partition. But that is history,

Asynchronous global index maintainence is the feature to clear up the orphan entries later (on demand) not by immediate, that means the oracle will defer the cleanup thus by provides faster index maintainence when there is a partition maintainence like truncate or drop etc.

But how about Oracle take care of those orphan entries

	1) Optimizer reads the entries by skipping those orphan entries and performs reads only that
	   contains valid key entries
	2) Oracle will not reuse those blocks for subsequent index entries until the clean up is performed
	3) A new column orphaned_entries in dba_indexes/part_indexes etc will show up with any orphan entries.

But, How to clean up those orphan entries?

	1) Automatic job PMO_DEFERRED_GIDX_MAINT_JOB  scheduled every 2.00 AM
	2) Perform manually alter index 
Now look at these Options
 Test Case 
	CREATE TABLE employees
	(employee_id NUMBER NOT NULL,
	last_name VARCHAR2(10),
	department_id NUMBER)
	PARTITION BY RANGE (department_id)
	(PARTITION employees_part1 VALUES LESS THAN (10000),
	PARTITION employees_part2 VALUES LESS THAN (20000) ,
	PARTITION employees_part3 VALUES LESS THAN (30000),
	PARTITION employees_part4 VALUES LESS THAN (40000) ,
	PARTITION employees_part5 VALUES LESS THAN (50000) ,
	PARTITION employees_part6 VALUES LESS THAN (60000)) tablespace users;



	 declare
	 v_no number :=1;
	 begin
	 delete employees;
	 for i in 1..59999 loop
	 insert into employees values(v_no,'name...',v_no);
	 v_no := v_no+1;
	 end loop;
	 commit;
	 end;
	 /
	A global index
	
	CREATE INDEX employees_global_idx ON employees(employee_id);
	
	A global partitioned index
	CREATE INDEX employees_global_part_idx ON employees(employee_id)
	GLOBAL PARTITION BY RANGE(employee_id)
	(PARTITION p1 VALUES LESS THAN(10000),
	 PARTITION p2 VALUES LESS THAN(20000),
	 PARTITION p3 VALUES LESS THAN(30000),
	 PARTITION p4 VALUES LESS THAN(40000),
	 PARTITION p5 VALUES LESS THAN(MAXVALUE));
 
 Check the index status  
 
	select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries from dba_indexes i, 
	dba_segments s where i.index_name = s.segment_name and table_name='EMPLOYEES' and partitioned = 'NO'
	union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries 
	from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'IDX%';

	INDEX_NAME                               PARTITION_NAME                             NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS   ORP
	---------------------------------------- ---------------------------------------- ---------- ---------- ----------- -------- ---
	EMPLOYEES_GLOBAL_IDX                                                                   59999        256         166 VALID    NO


 Option 2, Perform coalesce clean up 
Notes:- As you see below, the partition truncation operation create 9999 rows orphaned though we have

mentioned update global indexes, means indexes have not been rebuilt but deferred to later.
	SQL> alter table employees truncate partition employees_part1 update global indexes;

	Table truncated.

	Elapsed: 00:00:00.20
	SQL> analyze index EMPLOYEES_GLOBAL_IDX validate structure;

	Index analyzed.

	Elapsed: 00:00:00.09
	SQL>  select name, lf_rows, del_lf_rows from index_stats;

	NAME                               LF_ROWS    DEL_LF_ROWS
	--------------------------------- ---------- -----------
	EMPLOYEES_GLOBAL_IDX               59999        9999

	Elapsed: 00:00:00.05

	SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST', tabname=>'EMPLOYEES', cascade=>true, estimate_percent=>null,
	     method_opt=>'FOR ALL COLUMNS SIZE 1');

	SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries from dba_indexes i,
	  2  dba_segments s where i.index_name = s.segment_name and table_name='EMPLOYEES' and partitioned = 'NO'
	  3  union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries
	  4  from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'IDX%';


	INDEX_NAME                               PARTITION_NAME                             NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS   ORP
	---------------------------------------- ---------------------------------------- ---------- ---------- ----------- -------- ---
	EMPLOYEES_GLOBAL_IDX                                                                   50000        256         140 VALID    YES


	Elapsed: 00:00:01.21
	SQL>
	

	If you query the table for those entries that has been truncated, of course index usage has not been performed
	no rows will be returned and full table access performed scanning those partition only

	SQL> set autotrace on

	SQL> select * from employees where department_id between 100 and 2000;

	no rows selected

	Elapsed: 00:00:00.01

	Execution Plan
	----------------------------------------------------------
	Plan hash value: 3450816879

	----------------------------------------------------------------------------------------------------
	| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
	----------------------------------------------------------------------------------------------------
	|   0 | SELECT STATEMENT       |           |     1 |    33 |     2   (0)| 00:00:01 |       |       |
	|   1 |  PARTITION RANGE SINGLE|           |     1 |    33 |     2   (0)| 00:00:01 |     1 |     1 |
	|*  2 |   TABLE ACCESS FULL    | EMPLOYEES |     1 |    33 |     2   (0)| 00:00:01 |     1 |     1 |
	----------------------------------------------------------------------------------------------------

	Predicate Information (identified by operation id):
	---------------------------------------------------

	   2 - filter("DEPARTMENT_ID">=100 AND "DEPARTMENT_ID"< =2000)


	Statistics
	----------------------------------------------------------
		  0  recursive calls
		  0  db block gets
		  3  consistent gets
		  0  physical reads
		  0  redo size
		493  bytes sent via SQL*Net to client
		532  bytes received via SQL*Net from client
		  1  SQL*Net roundtrips to/from client
		  0  sorts (memory)
		  0  sorts (disk)
		  0  rows processed

	# perform the clean up (12c command)
	
	SQL> alter index EMPLOYEES_GLOBAL_IDX coalesce cleanup;
	
	Index altered.
	
	# orphaned column shows NO entries

	SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries from dba_indexes i,
	  2  dba_segments s where i.index_name = s.segment_name and table_name='EMPLOYEES' and partitioned = 'NO'
	  3  union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries
	  4  from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'IDX%';

	INDEX_NAME                               PARTITION_NAME                             NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS   ORP
	---------------------------------------- ---------------------------------------- ---------- ---------- ----------- -------- ---
	EMPLOYEES_GLOBAL_IDX                                                                   50000        256         140 VALID    NO

	# analyze the index to confirm no delete keys exists, yes it does not have any
	
	SQL> analyze index EMPLOYEES_GLOBAL_IDX validate structure;

	Index analyzed.

	Elapsed: 00:00:00.09

	SQL> select name, lf_rows, del_lf_rows from index_stats;

	NAME                         LF_ROWS DEL_LF_ROWS
	--------------------------------------- -----------
	EMPLOYEES_GLOBAL_IDX         50000      0

	Elapsed: 00:00:00.04
	SQL>

 Option 3, Perform dbms_part clean up operation 

	SQL> alter table employees truncate partition employees_part2 update global indexes;

	Table truncated.

	SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST', tabname=>'EMPLOYEES', cascade=>true, 
	     estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

	SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries from dba_indexes i,
	  2  dba_segments s where i.index_name = s.segment_name and table_name='EMPLOYEES' and partitioned = 'NO'
	  3  union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries
	  4  from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'IDX%';
	
	INDEX_NAME                               PARTITION_NAME                             NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS   ORP
	---------------------------------------- ---------------------------------------- ---------- ---------- ----------- -------- ---
	EMPLOYEES_GLOBAL_IDX                                                                   40000        256         112 VALID    YES

	
	SQL> analyze index EMPLOYEES_GLOBAL_IDX validate structure;

	Index analyzed.

	Elapsed: 00:00:00.03
	
	SQL> select name, lf_rows, del_lf_rows from index_stats;

	NAME                        LF_ROWS DEL_LF_ROWS
	-------------------------------------- -----------
	EMPLOYEES_GLOBAL_IDX        50000  10000

	Elapsed: 00:00:00.00
	
	SQL>
	
	# perform the clean up using dbms_part
	
	SQL> exec dbms_part.cleanup_gidx;

	PL/SQL procedure successfully completed.

	SQL> analyze index EMPLOYEES_GLOBAL_IDX validate structure;

	Index analyzed.

	Elapsed: 00:00:00.03
	SQL> select name, lf_rows, del_lf_rows from index_stats;

	NAME                        LF_ROWS DEL_LF_ROWS
	-------------------------------------- -----------
	EMPLOYEES_GLOBAL_IDX        50000        0

	SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries from dba_indexes i,
	  2  dba_segments s where i.index_name = s.segment_name and table_name='EMPLOYEES' and partitioned = 'NO'
	  3  union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries
	  4  from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'IDX%';
	
	INDEX_NAME                               PARTITION_NAME                             NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS   ORP
	---------------------------------------- ---------------------------------------- ---------- ---------- ----------- -------- ---
	EMPLOYEES_GLOBAL_IDX                                                                   40000        256         112 VALID    NO


 Option 1, Run the job manually,
	SQL> alter table employees truncate partition employees_part3 update global indexes;

	Table truncated.

	Elapsed: 00:00:00.20
	SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries from dba_indexes i,
	  2  dba_segments s where i.index_name = s.segment_name and table_name='EMPLOYEES' and partitioned = 'NO'
	  3  union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries
	  4  from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'IDX%';

	INDEX_NAME                               PARTITION_NAME                             NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS   ORP
	---------------------------------------- ---------------------------------------- ---------- ---------- ----------- -------- ---
	EMPLOYEES_GLOBAL_IDX                                                                   40000        256         112 VALID    YES

	Elapsed: 00:00:00.07
	SQL>
	
	As sys,
	
	SQL>  exec dbms_scheduler.run_job('PMO_DEFERRED_GIDX_MAINT_JOB');
	
	PL/SQL procedure successfully completed.
	
	Elapsed: 00:00:00.25
	SQL>


	SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries from dba_indexes i,
	  2     dba_segments s where i.index_name = s.segment_name and table_name='EMPLOYEES' and partitioned = 'NO'
	  3     union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries
	  4     from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'IDX%';
	
	INDEX_NAME                               PARTITION_NAME                             NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS   ORP
	---------------------------------------- ---------------------------------------- ---------- ---------- ----------- -------- ---
	EMPLOYEES_GLOBAL_IDX                                                                   30000        256          84 VALID    YES
	

 Option 4, Rebuild index manually, 
	SQL> alter index EMPLOYEES_GLOBAL_IDX rebuild online;

	Index altered.

	Elapsed: 00:00:01.73
	SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries from dba_indexes i,
	  2     dba_segments s where i.index_name = s.segment_name and table_name='EMPLOYEES' and partitioned = 'NO'
	  3     union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries
	  4     from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'IDX%';

	INDEX_NAME                               PARTITION_NAME                             NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS   ORP
	---------------------------------------- ---------------------------------------- ---------- ---------- ----------- -------- ---
	EMPLOYEES_GLOBAL_IDX                                                                   30000         96          84 VALID    NO

	Elapsed: 00:00:01.24
	SQL>

Conclusion,
	1) JOB removed orphan entries but not reduce HWM of index, but removed orphan entries

	2) Colease also did same thing what job had done and removed orphan entries

	3) Manual rebuild reduced the HWM of index from 256 blocks to 96 blocks and removed orphan entries.
-Thanks

Geek DBA

Reference: Richard foote blog, Oracle Documentation.

Query courtesy:Richard foote blog