Oracle 12 High Availability New Features Series: 1 - Partial Global/Local Indexes

Since its first introduction in Oracle 8.0 in 1997, Oracle has enhanced the functionality of Oracle
Partitioning with every release, by either adding new partitioning techniques, enhancing the scalability,
or extending the manageability and maintenance capabilities. Oracle Database 12c is no different by
offering enhanced composite partitioning strategies and vastly enhanced partition maintenance
operations.

Oracle 12c now provides the ability to index a subset of partitions and to exclude the others.

Local and global indexes can now be created on a subset of the partitions of a table. Partial Global indexes provide more flexibility in index creation for partitioned tables. For example, index segments can be omitted for the most recent partitions to ensure maximum data ingest rates without impacting the overall data model and access for the partitioned object.

Partial Global Indexes save space and improve performance during loads and queries. This feature supports global indexes that include or index a certain subset of table partitions or subpartitions, and exclude the others. This operation is supported using a default table indexing property. When a table is created or altered, a default indexing property can be specified for the table or its partitions.

The CREATE TABLE syntax is extended to support specification of the indexing attributes clause, which is as follows:
[INDEXING { ON | OFF }]

The indexing property determines whether table partitions and subpartitions are included in partial indexes on the table. The default is INDEXING ON.

When you specify the INDEXING clause at the table level, in the table properties clause, you set the default indexing property for the table.

The INDEXING clause may also be specified at the partition and subpartition levels (if ommitted partitions/subpartitions inherit the default indexing property of the table).

The CREATE INDEX syntax is extended to support specification of the FULL or PARTIAL index.

Specify INDEXING FULL to create a full index. In this case the index is decoupled from the table properties. A full index includes all partitions in the underlying table, regardless of their indexing properties.

Specify INDEXING PARTIAL to create a partial index. A partial index includes only partitions in the underlying table with an indexing property of ON.

The default is FULL, if neither FULL or PARTIAL is specified, i.e. Oracle will index all partitions (similar to the behavior of Oracle Database releases prior to Oracle Database 12c.).

CREATE TABLE myt1
(
c1 NUMBER(4),
c2 DATE,
c3 NUMBER(8)
)
INDEXING OFF
PARTITION BY RANGE (c2)
(
PARTITION p1 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) INDEXING ON,
PARTITION p2 VALUES LESS THAN (TO_DATE('01-JUL-2010','DD-MON-YYYY')) INDEXING OFF,
PARTITION p3 VALUES LESS THAN (TO_DATE('01-OCT-2011','DD-MON-YYYY')) INDEXING ON,
PARTITION p4 VALUES LESS THAN (TO_DATE('01-MAR-2012','DD-MON-YYYY')),
PARTITION p5 VALUES LESS THAN (TO_DATE('01-MAR-2013','DD-MON-YYYY'))
);

Partitions which have not INDEXING clause, inherit the default indexing property of the table (INDEXING OFF).

So:

Partitions P1 and P3 are included in all partial global indexes.

Local index partitions (for indexes created PARTIAL) corresponding to the above two table partitions are created usable by default

Other partitions are excluded from all partial global indexes, and created unusable in local indexes (for indexes created PARTIAL)

select partition_name,indexing from user_tab_partitions where table_name='MYT1';

PARTITION_NAME       INDEXING

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

P5                           OFF

P4                           OFF

P3                           ON

P2                           OFF

P1                           ON

select index_name, partition_name,STATUS from user_ind_partitions where index_name = 'LP_IDX';

INDEX_NAME                PARTITION_NAME  STATUS

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

lp_idx                              P2                            UNUSABLE

lp_idx                              P3                            USABLE

lp_idx                              P1                            USABLE

lp_idx                              P4                            UNUSABLE

lp_idx                              P5                            UNUSABLE

Note: Partitions P4 and P5 are not included in all partial global indexes because the default INDEXING property for the table is OFF, and INDEXING ON/OFF has not been specified at the partition level for those partitions; therefore, P4 and P5 partitions inherit the table default indexing property.

This statement will create a Partial Global index on the MYT1 table.

CREATE INDEX gp_idx ON myt1(c3) GLOBAL INDEXING PARTIAL;

select index_name,indexing from dba_indexes where index_name='GP_IDX';

INDEX_NAME                     INDEXING

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

GP_IDX                                PARTIAL

The GP_IDX index in the example will be created to index only those partitions that have INDEXING ON, and exclude the remaining partitions.