8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- Partial Indexes for Partitioned Tables
- All Partitioning Articles
- Partitioning Enhancements in Oracle Database 12c Release 1 (12.1)
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
.
We create a partitioned table with three partitions. The first two partitions have indexing turned on. One by default, and one explicitly. The third partition has indexing turned off.
-- 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 (date '2015-01-01'), partition part_2015 values less than (date '2016-01-01') indexing on, partition part_2016 values less than (date '2017-01-01') indexing off ); insert into t1 values (1, 't1 one', date '2014-07-01'); insert into t1 values (2, 't1 two', date '2015-07-01'); insert into t1 values (3, 't1 three', date '2016-07-01'); commit;
The INDEXING column of the USER_TAB_PARTITIONS
view shows us the indexing status of each partition. Only the 2016 partition has INDEXING turned off.
column table_name format a20 column partition_name format a20 select table_name, partition_name, indexing from user_tab_partitions where table_name = 'T1' order by 1,2; TABLE_NAME PARTITION_NAME INDE -------------------- -------------------- ---- T1 PART_2014 ON T1 PART_2015 ON T1 PART_2016 OFF SQL>
We change the indexing clause for a partition using the ALTER TABLE ... MODIFY PARTITION
command. We turn indexing off for the 2014 and 2015 partitions, and turn indexing on for the 2016 partition.
alter table t1 modify partition part_2014 indexing off; alter table t1 modify partition part_2015 indexing off; alter table t1 modify partition part_2016 indexing on;
We can see the changes reflected in the output from the USER_TAB_PARTITIONS
view. Indexing is off for the 2014 and 2015 partitions. Indexing is on for the 2016 partition.
select table_name, partition_name, indexing from user_tab_partitions where table_name = 'T1' order by 1,2; TABLE_NAME PARTITION_NAME INDE -------------------- -------------------- ---- T1 PART_2014 OFF T1 PART_2015 OFF T1 PART_2016 ON 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.
We create a local index on the CREATED_DATE
column. If we don't specify the INDEXING
clause during index creation, the default action is INDEXING FULL
.
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;
Checking the STATUS
column of the USER_IND_PARTITIONS
view we see all index partitions are usable. By default index creation ignores the INDEXING
setting of the table partitions.
column index_name format a25 select index_name, partition_name, status from user_ind_partitions where index_name = 'T1_LOCAL_PARTIAL_IDX' 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>
Checking the INDEXING
column of the USER_INDEXES
view we see the index is marked as indexing FULL
.
column indexing format a8 select index_name, indexing from user_indexes where index_name = 'T1_LOCAL_PARTIAL_IDX' order by 1; INDEX_NAME INDEXING ------------------------- -------- T1_LOCAL_PARTIAL_IDX FULL SQL>
We drop the index and create it again, this time using the INDEXING PARTIAL
clause.
drop index t1_local_partial_idx; create index t1_local_partial_idx on t1(created_date) local indexing partial;
Now we see the table partitions marked as INDEXING OFF
are not indexed, and have index partitions marked as unusable. The 2016 table partition was marked as INDEXING ON
, so it is indexed and we see a usable index partition.
select index_name, partition_name, status from user_ind_partitions where index_name = 'T1_LOCAL_PARTIAL_IDX' order by 1,2; INDEX_NAME PARTITION_NAME STATUS ------------------------- -------------------- -------- T1_LOCAL_PARTIAL_IDX PART_2014 UNUSABLE T1_LOCAL_PARTIAL_IDX PART_2015 UNUSABLE T1_LOCAL_PARTIAL_IDX PART_2016 USABLE SQL>
Checking the INDEXING
column of the USER_INDEXES
view we see the index is marked as indexing PARTIAL
.
column indexing format a8 select index_name, indexing from user_indexes where index_name = 'T1_LOCAL_PARTIAL_IDX' order by 1; INDEX_NAME INDEXING ------------------------- -------- T1_LOCAL_PARTIAL_IDX PARTIAL SQL>
Global indexes can also be created as partial indexes, with only the flagged partitions included in the index. We create a partial global index. Notice the use of the indexing partial clause. The resulting global index is marked as INDEXING PARTIAL
.
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 where index_name like 'T1%' order by 1; INDEX_NAME INDEXING ------------------------- -------- T1_GLOBAL_PARTIAL_IDX PARTIAL T1_LOCAL_PARTIAL_IDX PARTIAL SQL>
Modified Views
The {DBA|ALL|USER}_PART_TABLES
, {DBA|ALL|USER}_TAB_PARTITIONS
and {DBA|ALL|USER}_TAB_SUBPARTITIONS
views have been modified to include an INDEXING
column, which indicates if indexing is ON
or OFF
for the partition.
The {DBA|ALL|USER}_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
- Partial Indexes for Partitioned Tables
- All Partitioning Articles
- Partitioning Enhancements in Oracle Database 12c Release 1 (12.1)
Hope this helps. Regards Tim...