12c - Partial Index for Partitioned table

A new feature called  Partial Indexes for Partitioned Tables  was introduced in Oracle 12c.

Now, you can create the indexes (global or local) for specific partitions or sub-partitions, that is,   the indexes will be created only for partition / sub-partition you want.

This feature is not supported for unique indexes, ie to indices used to enforce unique constraints.

New attributes to the command  CREATE TABLE :

• INDEXING ON (default)
• INDEXING OFF

New attributes to the command  CREATE INDEX :

• INDEXING FULL (default)
• INDEXING PARTIAL

As this subject is very extensive, with several variations, I'll show just a few examples of how to start using this new feature.

Creating the table for testing:

create table TABLE_TEST
(
Column1 number,
Column2 number
)
indexing on
partition by range (column1)
(
partition part1 values less than (100) indexing off
partition PART2 values less than (200) indexing on,
partition part3 values less than (300)
values less than part4 partition (400)
);
Table created.

Checking the attribute  indexing :

SQL> select table_name, def_indexing DEF

       from dba_part_tables

      where table_name = 'TABLE_TEST';

TABLE_NAME DEF

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

TABLE_TEST ON

Checking the attribute indexing by partition:

SQL> select table_name, partition_name, indexing
       from dba_tab_partitions
      where table_name = 'TABLE_TEST'
   order by partition_position;

TABLE_NAME partition_name INDEXING

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

TABLE_TEST PART1 OFF

TABLE_TEST PART2 ON

TABLE_TEST PART3 ON

TABLE_TEST part4 ON

Changing the attribute  indexing  after the created table:

SQL> alter table TABLE_TEST modify default attributes indexing off;

Table altered.


SQL> select table_name, def_indexing
      from dba_part_tables
     where table_name = 'TABLE_TEST';

TABLE_NAME DEF
-------------------- ---
TABLE_TEST OFF

Changing the attribute  indexing  the partition  after the created table:

SQL> alter table TABLE_TEST modify partition part3 indexing off;

Table altered.


SQL> select table_name, partition_name, indexing
       from dba_tab_partitions
      where table_name = 'TABLE_TEST'
   order by partition_position;


TABLE_NAME partition_name INDEXING

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

TABLE_TEST PART1          OFF               

TABLE_TEST  PART2         ON                

TABLE_TEST  PART3         OFF                

TABLE_TEST  part4         ON                

Creating an index:

SQL> create index TABLE_TEST_INDEX01 on TABLE_TEST (Column1) Local partial indexing;

Index created.

SQL> select index_name, partition_name, status
       from dba_ind_partitions 

      where index_name = 'TABLE_TEST_INDEX01'
   order by partition_position;

INDEX_NAME partition_name STATUS
-------------------- -------------------- ------- -

TABLE_TEST_INDEX01 PART1 UNUSABLE

TABLE_TEST_INDEX01 PART2        USABLE         

TABLE_TEST_INDEX01 PART3        UNUSABLE         

TABLE_TEST_INDEX01 part4        USABLE       

Effect on the execution plan: