Partial Indexes for Partitioned Tables Oracle Database 12C release 1 (12.1)

Partial indexing enables to create local and global indexes on a subset of partitions, subpartitions of a table. When a table is created or altered, a default indexing property can be specified for the table or its partitions.

New attribute INDEXING ON|OFF can be specified on table(it becomes default attribute for its partitions and subpartitions), partition(it becomes default attribute for its subpartitions) or subpartition level. The attribute is important only for partial indexing.

  • INDEXING ON (default) – tells to consider data for partial indexing
  • INDEXING OFF – tells to ignore data for partial indexing.

New attribute INDEXING PARTIAL|FULL can be specified during index creation

  • INDEXING FULL (default) – in this case data from all table partitions/subpartitions are used during index creation no matter of partition/subpartition attribute INDEXING ON/OFF.
  • INDEXING PARTIAL – in this case only data from partitions/subpartitions with INDEXING ON are used during index creation and data from partitions/subpartitions with INDEXING OFF are ignored.

In following example I created table test_tbl with default attribute INDEXING ON (which is default anyway :)) on table level and INDEXING ON|OFF on some partitions of the table.

create table test_tbl
(
  id1 number,
  id2 number,
  id3 number,
  id4 number
)
indexing on
partition by range(id1)
(
  partition p1 values less than(1) indexing off,
  partition p2 values less than(2) indexing on,
  partition p3 values less than(3),
  partition p4 values less than(4),
  partition p5 values less than(5)
);

The settings are visible in standard views in new column DEF_INDEXING, INDEXING

DBA|ALL|USER_PART_TABLES

select table_name, def_indexing
from user_part_tables
where table_name='TEST_TBL';

TABLE_NAME    DEF_INDEXING 
------------- -------------
TEST_TBL      ON

and DBA|ALL|USER_TAB_PARTITIONS

select table_name, partition_name, indexing
from user_tab_partitions
where table_name='TEST_TBL'
order by partition_position;

TABLE_NAME  PARTITION_NAME INDEXING
----------- -------------- --------
TEST_TBL    P1             OFF     
TEST_TBL    P2             ON      
TEST_TBL    P3             ON      
TEST_TBL    P4             ON      
TEST_TBL    P5             ON

default indexing attribute for table can be change using ALTER TABLE command. It has impact only on new partitions.

alter table test_tbl
modify default attributes indexing off;

select table_name, def_indexing
from user_part_tables
where table_name='TEST_TBL';

TABLE_NAME    DEF_INDEXING 
------------- -------------
TEST_TBL      OFF

alter table test_tbl
add partition p6 values less than(6);

select table_name, partition_name, indexing
from user_tab_partitions
where table_name='TEST_TBL'
order by partition_position;

TABLE_NAME  PARTITION_NAME INDEXING
----------- -------------- --------
TEST_TBL    P1             OFF     
TEST_TBL    P2             ON      
TEST_TBL    P3             ON      
TEST_TBL    P4             ON      
TEST_TBL    P5             ON
TEST_TBL    P6             OFF

as well indexing attribute can be changed on partition level using ALTER TABLE command

alter table test_tbl 
modify partition p3 indexing off;

select table_name, partition_name, indexing
from user_tab_partitions
where table_name='TEST_TBL'
order by partition_position;

TABLE_NAME  PARTITION_NAME INDEXING
----------- -------------- --------
TEST_TBL    P1             OFF     
TEST_TBL    P2             ON      
TEST_TBL    P3             OFF
TEST_TBL    P4             ON      
TEST_TBL    P5             ON
TEST_TBL    P6             OFF

Now it’s time to create indexes on the table.New clause INDEXING PARTIAL for index creation reads attributes INDEXING for each partitions and create segments with status UNUSABLE for partitions with INDEXING=OFF.

create index test_tbl_idx_1 on test_tbl(id1) local indexing partial;

select index_name, partition_name, status 
from user_ind_partitions 
where index_name='TEST_TBL_IDX_1'
order by partition_position;

INDEX_NAME      PARTITION_NAME  STATUS 
--------------- --------------- --------
TEST_TBL_IDX_1  P1              UNUSABLE 
TEST_TBL_IDX_1  P2              USABLE   
TEST_TBL_IDX_1  P3              UNUSABLE 
TEST_TBL_IDX_1  P4              USABLE   
TEST_TBL_IDX_1  P5              USABLE   
TEST_TBL_IDX_1  P6              UNUSABLE

create index test_tbl_idx_2 on test_tbl(id2, id3) 
global
partition by hash(id2) partitions 2 indexing partial;

select index_name, partition_name, status 
from user_ind_partitions 
where index_name='TEST_TBL_IDX_2'
order by partition_position;

INDEX_NAME      PARTITION_NAME  STATUS 
--------------- --------------- --------
TEST_TBL_IDX_2  SYS_P444        USABLE 
TEST_TBL_IDX_2  SYS_P445        USABLE

If you change attribute on partition level to INDEXING OFF automatically corresponding partitions for local indexes becomes UNUSABLE

alter table test_tbl
modify partition p4 indexing off;

select index_name, partition_name, status 
from user_ind_partitions 
where index_name='TEST_TBL_IDX_1'
order by partition_position;

INDEX_NAME      PARTITION_NAME  STATUS 
--------------- --------------- --------
TEST_TBL_IDX_1  P1              UNUSABLE 
TEST_TBL_IDX_1  P2              USABLE   
TEST_TBL_IDX_1  P3              UNUSABLE 
TEST_TBL_IDX_1  P4              UNUSABLE   
TEST_TBL_IDX_1  P5              USABLE   
TEST_TBL_IDX_1  P6              UNUSABLE

and vice versa INDEXING ON will automatically rebuild partitions for partial indexes

alter table test_tbl
modify partition p4 indexing on;

select index_name, partition_name, status 
from user_ind_partitions 
where index_name='TEST_TBL_IDX_1'
order by partition_position;

INDEX_NAME      PARTITION_NAME  STATUS 
--------------- --------------- --------
TEST_TBL_IDX_1  P1              UNUSABLE 
TEST_TBL_IDX_1  P2              USABLE   
TEST_TBL_IDX_1  P3              UNUSABLE 
TEST_TBL_IDX_1  P4              USABLE   
TEST_TBL_IDX_1  P5              USABLE   
TEST_TBL_IDX_1  P6              UNUSABLE

Global indexes are maintained automatically during switching INDEXING attribute

select index_name, partition_name, status 
from user_ind_partitions 
where index_name='TEST_TBL_IDX_2'
order by partition_position;

INDEX_NAME      PARTITION_NAME  STATUS 
--------------- --------------- --------
TEST_TBL_IDX_2  SYS_P422        USABLE 
TEST_TBL_IDX_2  SYS_P422        USABLE

Creation of indexes with clause INDEXING FULL will ignore partition attribute INDEXING. In fact INDEXING FULL can be skipped it’s default value during creation of new index.

create index test_tbl_idx_3 on test_tbl(id2) local indexing full; 
create index test_tbl_idx_4 on test_tbl(id3) local;

select index_name, partition_name, status 
from user_ind_partitions 
where index_name='TEST_TBL_IDX_3'
order by partition_position;

INDEX_NAME      PARTITION_NAME  STATUS 
--------------- --------------- --------
TEST_TBL_IDX_3  P1              USABLE   
TEST_TBL_IDX_3  P2              USABLE   
TEST_TBL_IDX_3  P3              USABLE   
TEST_TBL_IDX_3  P4              USABLE   
TEST_TBL_IDX_3  P5              USABLE   
TEST_TBL_IDX_3  P6              USABLE

select index_name, partition_name, status 
from user_ind_partitions 
where index_name='TEST_TBL_IDX_4'
order by partition_position;

INDEX_NAME      PARTITION_NAME  STATUS 
--------------- --------------- --------
TEST_TBL_IDX_4  P1              USABLE 
TEST_TBL_IDX_4  P2              USABLE   
TEST_TBL_IDX_4  P3              USABLE 
TEST_TBL_IDX_4  P4              USABLE   
TEST_TBL_IDX_4  P5              USABLE   
TEST_TBL_IDX_4  P6              USABLE

Restrictions on Partial Indexes:

  • The underlying table of a partial index cannot be a nonpartitioned table.
  • Unique indexes cannot be partial indexes. This applies to indexes created with the CREATE UNIQUE INDEX statement and indexes that are implicitly created when you specify a unique constraint on one or more columns

Have a fun 🙂
Tomasz

4 thoughts on “Partial Indexes for Partitioned Tables Oracle Database 12C release 1 (12.1)

  1. Is it same as –making local index partittion or subpartiton– unusable
    ie
    ALTER INDEX indexName MODIFY subPARTITION local_subpartition_indexname UNUSABLE

    if they are not same
    how making index unusable differs than INDEXING PARTIAL

    • Generally there is no difference here PARTIAL and UNUSABLE in effect will give the same but it’s easier to manage indexes using INDEXING PARTIAL than playing with UNUSABLE and REBUILD manually. Simple example switching from INDEXING OFF to INDEXING ON automatically rebuild partitions with UNUSABLE and vice versa. It’s huge benefit easier management, easier syntax.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.