Partitioning -- 3d : Partial Indexing (in 11g)

Oracle 12c has introduced a new feature called "Partial Index" whereby selective partitions of a Table are indexed.  This is useful, for example, where you have a large historical table and you know that older Partitions are infrequently accessed and no longer need to be indexed.  For such tables, you can afford to "lose" the index for these older Partitions.

How would you do this in 11.2 ?

Let me go back to the SALES_DATA table with data from 2016 to 2018 populated.  This is the status of the index partition segments :

SQL> l
  1  select segment_name, partition_name, bytes/1024
  2  from user_segments
  3  where segment_type = 'INDEX PARTITION'
  4  and segment_name in
  5  (select index_name
  6   from user_indexes
  7   where table_name = 'SALES_DATA')
  8* order by 1,2
SQL> /

SEGMENT_NAME                   PARTITION_NA BYTES/1024
------------------------------ ------------ ----------
SALES_DATA_LCL_NDX_1           P_2016             3072
SALES_DATA_LCL_NDX_1           P_2017             3072
SALES_DATA_LCL_NDX_1           P_2018             3072
SALES_DATA_LCL_NDX_2           P_2016               64
SALES_DATA_LCL_NDX_2           P_2017               64
SALES_DATA_LCL_NDX_2           P_2018               64

6 rows selected.

SQL> 

So, if I now want to "unindex" the year 2016 partition (P_2016) of the SALES_DATA table, I can :

SQL> show parameter deferred_segment_creation

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

SQL> alter index SALES_DATA_LCL_NDX_1 modify partition P_2016 unusable;

Index altered.

SQL> alter index SALES_DATA_LCL_NDX_2 modify partition P_2016 unusable;

Index altered.

SQL> 
SQL> l
  1  select segment_name, partition_name, bytes/1024
  2  from user_segments
  3  where segment_type = 'INDEX PARTITION'
  4  and segment_name in
  5  (select index_name
  6   from user_indexes
  7   where table_name = 'SALES_DATA')
  8* order by 1,2
SQL> /

SEGMENT_NAME                   PARTITION_NA BYTES/1024
------------------------------ ------------ ----------
SALES_DATA_LCL_NDX_1           P_2017             3072
SALES_DATA_LCL_NDX_1           P_2018             3072
SALES_DATA_LCL_NDX_2           P_2017               64
SALES_DATA_LCL_NDX_2           P_2018               64

SQL> 
SQL> select  count(*) from sales_data partition (P_2016);

  COUNT(*)
----------
    100000

SQL>               

You will notice that although the P_2016 Partition in the Table has data, the corresponding Index Partition no longer has a segment -- no space is allocated to it  (although the logical definition of the index exists).  This is possible with the "deferred_segment_creation" parameter set to TRUE in 11g.

In fact, you will notice that although the table has Partitions for 2019 and 2020 and MAXVALUE, corresponding Index Partition Segments do not exist (because no data has been inserted into those Table Partitions yet) !

SQL> select partition_name           
  2  from user_tab_partitions
  3  where table_name = 'SALES_DATA'
  4  order by partition_position
  5  /

PARTITION_NA
------------
P_2016
P_2017
P_2018
P_2019
P_2020
P_MAXVALUE

6 rows selected.

SQL> 
SQL> select index_name, partition_name, status
  2  from user_ind_partitions
  3  where index_name in
  4  (select index_name
  5   from user_indexes
  6   where table_name = 'SALES_DATA')
  7  order by index_name, partition_position
  8  /

INDEX_NAME                     PARTITION_NA STATUS
------------------------------ ------------ --------
SALES_DATA_LCL_NDX_1           P_2016       UNUSABLE
SALES_DATA_LCL_NDX_1           P_2017       USABLE
SALES_DATA_LCL_NDX_1           P_2018       USABLE
SALES_DATA_LCL_NDX_1           P_2019       USABLE
SALES_DATA_LCL_NDX_1           P_2020       USABLE
SALES_DATA_LCL_NDX_1           P_MAXVALUE   USABLE
SALES_DATA_LCL_NDX_2           P_2016       UNUSABLE
SALES_DATA_LCL_NDX_2           P_2017       USABLE
SALES_DATA_LCL_NDX_2           P_2018       USABLE
SALES_DATA_LCL_NDX_2           P_2019       USABLE
SALES_DATA_LCL_NDX_2           P_2020       USABLE
SALES_DATA_LCL_NDX_2           P_MAXVALUE   USABLE

12 rows selected.

SQL> 

This behaviour is a consequence of "deferred_segment_creation".

Note : If a Partitioned Index is a Unique / Primary Key Index, do NOT attempt to set an Index Partition to UNUSABLE.  UNUSABLE status would prevent INSERTs into the table.