Partial Indexes for Partitioned Tables in Oracle Database 12c Release 1

Oracle 12c allows the creation of global and local indexes on a subset of the partitions of a partitioned table.

Table : INDEXING [ON | OFF] Clause
Index : INDEXING [FULL| PARTIAL] Clause
Modified Views

Table: INDEXING [ON | OFF] Clause

The decision about which partitions are indexed is made using the INDEXING [ON | OFF] clause associated with each partition, with the default being INDEXING ON.

-- Create and populate the partitioned table.
DROP TABLE t1 PURGE;

CREATE TABLE t1
(id            NUMBER,
 description   VARCHAR2(50),
 created_date  DATE)
PARTITION BY RANGE (created_date)
(PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')),
 PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) INDEXING ON,
 PARTITION part_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')) INDEXING OFF);

INSERT INTO t1 VALUES (1, 't1 ONE', TO_DATE('01/07/2014', 'DD/MM/YYYY'));
INSERT INTO t1 VALUES (2, 't1 TWO', TO_DATE('01/07/2015', 'DD/MM/YYYY'));
INSERT INTO t1 VALUES (3, 't1 THREE', TO_DATE('01/07/2016', 'DD/MM/YYYY'));
COMMIT;


-- Check the indexing status of each partition.
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20

SELECT table_name,
       partition_name,
       indexing
FROM   user_tab_partitions
ORDER BY 1,2;

TABLE_NAME           PARTITION_NAME       INDE
-------------------- -------------------- ----
T1                   PART_2014            ON
T1                   PART_2015            ON
T1                   PART_2016            OFF

SQL>

The indexing setting can be reset using the ALTER TABLE ... MODIFY PARTITION command.

ALTER TABLE t1 MODIFY PARTITION part_2014 INDEXING OFF;

SELECT table_name,
       partition_name,
       indexing
FROM   user_tab_partitions
ORDER BY 1,2;

TABLE_NAME           PARTITION_NAME       INDE
-------------------- -------------------- ----
T1                   PART_2014            OFF
T1                   PART_2015            ON
T1                   PART_2016            OFF

SQL>


ALTER TABLE t1 MODIFY PARTITION part_2014 INDEXING ON;

TABLE_NAME           PARTITION_NAME       INDE
-------------------- -------------------- ----
T1                   PART_2014            ON
T1                   PART_2015            ON
T1                   PART_2016            OFF

SQL>

Index: INDEXING [FULL | PARTIAL] Clause

Even with the partition indexing settings in place, by default indexes are created as INDEXING FULL, so the partition setting are ignored.

-- Create a full local index.
DROP INDEX t1_local_partial_idx;
-- CREATE INDEX t1_local_partial_idx ON t1(created_date) LOCAL INDEXING FULL;
CREATE INDEX t1_local_partial_idx ON t1(created_date) LOCAL;


-- Check the status of the index partitions.
COLUMN index_name FORMAT A25

SELECT index_name,
       partition_name,
       status
FROM   user_ind_partitions
ORDER BY 1,2;

INDEX_NAME                PARTITION_NAME       STATUS
------------------------- -------------------- --------
T1_LOCAL_PARTIAL_IDX      PART_2014            USABLE
T1_LOCAL_PARTIAL_IDX      PART_2015            USABLE
T1_LOCAL_PARTIAL_IDX      PART_2016            USABLE

SQL>


-- Check the indexing status of the index.
SELECT index_name,
       indexing
FROM   user_indexes
ORDER BY 1;

INDEX_NAME                INDEXIN
------------------------- -------
T1_LOCAL_PARTIAL_IDX      FULL

SQL>

Adding the INDEXING PARTIAL clause allows the index to be created as a partial index. The non-indexed partitions are flagged as unusable.

-- Create a partial local index.
DROP INDEX t1_local_partial_idx;
CREATE INDEX t1_local_partial_idx ON t1(created_date) LOCAL INDEXING PARTIAL;


-- Check the status of the index partitions.
SELECT index_name,
       partition_name,
       status
FROM   user_ind_partitions
ORDER BY 1,2;

INDEX_NAME                PARTITION_NAME       STATUS
------------------------- -------------------- --------
T1_LOCAL_PARTIAL_IDX      PART_2014            USABLE
T1_LOCAL_PARTIAL_IDX      PART_2015            USABLE
T1_LOCAL_PARTIAL_IDX      PART_2016            UNUSABLE

SQL>


-- Check the indexing status of the index.
SELECT index_name,
       indexing
FROM   user_indexes
ORDER BY 1;

INDEX_NAME                INDEXIN
------------------------- -------
T1_LOCAL_PARTIAL_IDX      PARTIAL

SQL>

Global indexes can also be created as partial indexes, with only the flagged partitions included in the index.

-- Create a partial global index.
CREATE INDEX t1_global_partial_idx ON t1(description) GLOBAL INDEXING PARTIAL;


-- Check the indexing status of the indexes.
SELECT index_name,
       indexing
FROM   user_indexes
ORDER BY 1;

INDEX_NAME                INDEXIN
------------------------- -------
T1_GLOBAL_PARTIAL_IDX     PARTIAL
T1_LOCAL_PARTIAL_IDX      PARTIAL

SQL>

Modified Views

The *_PART_TABLES, *_TAB_PARTITIONS and *_TAB_SUBPARTITIONS views have been modified to include an INDEXING column, which indicates if indexing is ON or OFF for the partition.

The *_INDEXES view has been modified to include an INDEXING column, which indicates if the index is FULL or PARTIAL.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.