Wednesday, June 26, 2013

Oracle 12c New Features - Partial Indexing on Partitioned Tables

Oracle 12c allows DBAs to set specific table partitions "INDEXING OFF" and create indexes with "INDEXING PARTIAL".  This means that index partitions won't be created for the specified table partitions.  This allows DBAs to have multiple indexing strategies for different partitions, or to rapidly create indexes on specific partitions.

Example :- create a partitioned table :-

SQL> CREATE TABLE test_range
(id  NUMBER(5),
att1 char(1),
att2 char(1),
att3 char(1))
PARTITION BY RANGE(id)
(
PARTITION id_10 VALUES LESS THAN(10),
PARTITION id_20 VALUES LESS THAN(20)
);

Table created.

Create a local index.  By default it will create index partitions for every table partition.

SQL> create index test_index1 on test_range(att1) local;

Index created.

Now set indexing off for one partition, and create a second index with partial indexing enabled.

SQL> alter table test_range modify partition id_20 indexing off;

Table altered.

SQL> create index test_index2 on test_range(att2) local indexing partial;

Index created.

Insert to create the segments.

SQL> insert into test_range values (1,'a','a','a');

1 row created.

SQL> insert into test_range values (11,'b','b','b');

1 row created.

SQL> commit;

Commit complete.

And we can see that for index TEST_INDEX2 with PARTIAL INDEXING set, no segment was created for table partition ID_20 with INDEXING OFF.

SQL> select index_name, partition_name, segment_created from dba_ind_partitions where index_owner = 'TEST';

INDEX_NAME
--------------------------------------------------------------------------------
PARTITION_NAME
--------------------------------------------------------------------------------
SEGMENT_CREATED
-------------------------
TEST_INDEX1
ID_10
YES

TEST_INDEX1
ID_20
YES

TEST_INDEX2
ID_10
YES

TEST_INDEX2
ID_20
NO

SQL> select segment_name, segment_type, count(*) from dba_segments where owner = 'TEST' group by segment_name, segment_type;

SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE         COUNT(*)
------------------ ----------
TEST_RANGE
TABLE PARTITION             2

TEST_INDEX1
INDEX PARTITION             2

TEST_INDEX2
INDEX PARTITION             1