12c Database : Partial Indexing on partition/sub partitions tables

Before to 12c, it is not possible to create a index for certain partitions or for the partitions you want.

From 12c onwards, you can create the indexes (global or local) for specific partitions (not to all partitions) or for specific sub partitions (not to all sub partitions). This means indexes will be created for only partitions that you want.

In order to use this feature,

   at Table level new option INDEXING ON|OFF  has bee introduced in "Create table" statement.
	INDEXING ON is default.
	
   at Index level INDEXING ON|OFF|PARTIAL|FULL has bee introduced in "Create Index" statement.
	INDEXING FULL is default   

   Restrictions:-
   a) Table cannot be a non partitioned table
   b) You cannot use this feature with non unique index

Let's create a partitioned table with different options above and see how our indexing looks like

	SQL> create table part_tbl
	  2  (
	  3    id1 number,
	  4    id2 number,
	  5    id3 number,
	  6    id4 number
	  7  )
	  8  indexing on
	  9  partition by range(id1)
	 10  (
	 11    partition p1 values less than(1) indexing off,
	 12    partition p2 values less than(2) indexing on,
	 13    partition p3 values less than(3),
	 14    partition p4 values less than(4),
	 15    partition p5 values less than(5)
	) tablespace users; 16

	Table created.

As you see few new options above,

	At line 8, table level indexing on is mentioned which means partial 
	indexing feature will be used if required.
	
	At line 11, for partition p1 indexing off has been used, which means
	for this partition index will need not to be created 
	if index is going to be created in future
	
	At line 12, for partition p2 indexing off has been used, which means
	for this partition index will created if index is 
	going to be created in future

To view this functionality, a new columns has been added to

	dba_part_tables - def_indexing
	dba_tab_partitions - indexing.
	
	Lets check our case,
	
	SQL> select table_name, def_indexing
	from user_part_tables
	where table_name='PART_TBL';
 
	TABLE_NAME           DEF
	-------------------- ---
	PART_TBL             ON
	
	SQL>
	SQL>
	SQL> select table_name, partition_name, indexing
	from user_tab_partitions
	where table_name='PART_TBL'
	order by partition_position;
	 
	TABLE_NAME           PARTITION_NAME                 INDE
	-------------------- ------------------------------ ----
	PART_TBL             P1                             OFF
	PART_TBL             P2                             ON
	PART_TBL             P3                             ON
	PART_TBL             P4                             ON
	PART_TBL             P5                             ON
	
	SQL>

As you see, the Table indexing feature is on and the partition p1 is off and p2 is on.

To modify the attribute of partial indexing you can use alter statement

	SQL> alter table part_tbl modify default attributes indexing off;

	Table altered.

Now check the status of partial indexing feature, its been marked off now.

	SQL> select table_name, def_indexing
	from user_part_tables
	where table_name='PART_TBL';
	  2    3
	TABLE_NAME           DEF
	-------------------- ---
	PART_TBL             OFF

Lets take a look at partition level, the existing partitions does not been marked on (P1)

	SQL>  select table_name, partition_name, indexing
	from user_tab_partitions
	where table_name='PART_TBL'
	order by partition_position;
	  2    3    4
	TABLE_NAME           PARTITION_NAME                 INDE
	-------------------- ------------------------------ ----
	PART_TBL             P1                             OFF
	PART_TBL             P2                             ON
	PART_TBL             P3                             ON
	PART_TBL             P4                             ON
	PART_TBL             P5                             ON

	SQL>

So the altering the attribute will not work for existing, lets add a partition and see.

	SQL> alter table part_tbl add partition p6 values less than(6);

	Table altered.

	SQL> select table_name, partition_name, indexing
	from user_tab_partitions
	where table_name='PART_TBL'
	order by partition_position;  2    3    4

	TABLE_NAME           PARTITION_NAME                 INDE
	-------------------- ------------------------------ ----
	PART_TBL             P1                             OFF
	PART_TBL             P2                             ON
	PART_TBL             P3                             ON
	PART_TBL             P4                             ON
	PART_TBL             P5                             ON
	PART_TBL             P6                             OFF

	6 rows selected.

	SQL>

As you saw the partition P6 has been now off even though I have not marked indexing off.

Let do one more check whether the existing partition level partial indexing feature
can be switched on though we have table level is off, yes that works

	SQL> alter table part_tbl modify partition p3 indexing off;
	
	Table altered.
	
	SQL> select table_name, partition_name, indexing
	from user_tab_partitions
	where table_name='PART_TBL'
	order by partition_position;  2    3    4
	
	TABLE_NAME           PARTITION_NAME                 INDE
	-------------------- ------------------------------ ----
	PART_TBL             P1                             OFF
	PART_TBL             P2                             ON
	PART_TBL             P3                             OFF
	PART_TBL             P4                             ON
	PART_TBL             P5                             ON
	PART_TBL             P6                             OFF
	
	6 rows selected.
	
	SQL>

We have explored the feature at definition level as of now,

Now, Let's add the Local indexes and see it behaviour.

	SQL> create index part_tbl_idx_1 on part_tbl(id1) local indexing partial;

	Index created.

	SQL> select index_name, partition_name, status
	from user_ind_partitions
	where index_name='PART_TBL_IDX_1'
	order by partition_position;
	  2    3    4
	INDEX_NAME                     PARTITION_NAME                           STATUS
	------------------------------ ---------------------------------------- ---------- 
	PART_TBL_IDX_1                 P1                                       UNUSABLE
	PART_TBL_IDX_1                 P2                                       USABLE
	PART_TBL_IDX_1                 P3                                       UNUSABLE
	PART_TBL_IDX_1                 P4                                       USABLE
	PART_TBL_IDX_1                 P5                                       USABLE
	PART_TBL_IDX_1                 P6                                       UNUSABLE

	6 rows selected.

Few things to note,the partitions(p1,p3,p6) if you see were marked off before were unusable now.

Let's add the global index and see its behaviour,as i mentioned partial

	SQL> create index part_tbl_idx_2 on part_tbl(id2, id3)
	global partition by hash(id2) partitions 2 indexing partial;

	Index created.

	SQL> select index_name, partition_name, status
	from user_ind_partitions
	where index_name='PART_TBL_IDX_2'
	order by partition_position;

	INDEX_NAME                     PARTITION_NAME       STATUS
	------------------------------ -----------------------
	PART_TBL_IDX_2                 SYS_P361             USABLE
	PART_TBL_IDX_2                 SYS_P362             USABLE
	
	Global hash partitioned index on id2 and id3 columns are usable
	

If you mark any partition indexing as off, the corresponding local index partition will be marked unusable.

	SQL> alter table part_tbl modify partition p4 indexing off  2  ;

	Table altered.

	SQL> select index_name, partition_name, status
	from user_ind_partitions
	where index_name='PART_TBL_IDX_1'
	order by partition_position;
	  2    3    4
	INDEX_NAME                     PARTITION_NAME                           STATUS
	------------------------------ ---------------------------------------- 
	PART_TBL_IDX_1                 P1                                       UNUSABLE
	PART_TBL_IDX_1                 P2                                       USABLE
	PART_TBL_IDX_1                 P3                                       UNUSABLE
	PART_TBL_IDX_1                 P4                                       UNUSABLE
	PART_TBL_IDX_1                 P5                                       USABLE
	PART_TBL_IDX_1                 P6                                       UNUSABLE

	6 rows selected.

	SQL>

Similarly, if you marked the partition level indexing off ,the index will rebuild and become usable.

	SQL> alter table part_tbl modify partition p4 indexing on;
	Table altered.


	SQL> select index_name, partition_name, status
	from user_ind_partitions
	where index_name='PART_TBL_IDX_1'
	order by partition_position;
	  2    3    4
	INDEX_NAME                     PARTITION_NAME                           STATUS
	------------------------------ ---------------------------------------- 
	PART_TBL_IDX_1                 P1                                       UNUSABLE
	PART_TBL_IDX_1                 P2                                       USABLE
	PART_TBL_IDX_1                 P3                                       UNUSABLE
	PART_TBL_IDX_1                 P4                                       USABLE
	PART_TBL_IDX_1                 P5                                       USABLE
	PART_TBL_IDX_1                 P6                                       UNUSABLE

	6 rows selected.

	As you saw the P4 partitioned index has been marked usable back.
	

Hope this helps

-Thanks
Geek DBA