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...