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.
Related articles.
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:
- Partial Indexes for Partitioned Tables
- All Partitioning Articles
- Partitioning Enhancements in Oracle Database 12c Release 1 (12.1)
Hope this helps. Regards Tim...