Partial indexing enables to create local and global indexes on a subset of partitions, subpartitions of a table. When a table is created or altered, a default indexing property can be specified for the table or its partitions.
New attribute INDEXING ON|OFF can be specified on table(it becomes default attribute for its partitions and subpartitions), partition(it becomes default attribute for its subpartitions) or subpartition level. The attribute is important only for partial indexing.
- INDEXING ON (default) – tells to consider data for partial indexing
- INDEXING OFF – tells to ignore data for partial indexing.
New attribute INDEXING PARTIAL|FULL can be specified during index creation
- INDEXING FULL (default) – in this case data from all table partitions/subpartitions are used during index creation no matter of partition/subpartition attribute INDEXING ON/OFF.
- INDEXING PARTIAL – in this case only data from partitions/subpartitions with INDEXING ON are used during index creation and data from partitions/subpartitions with INDEXING OFF are ignored.
In following example I created table test_tbl with default attribute INDEXING ON (which is default anyway :)) on table level and INDEXING ON|OFF on some partitions of the table.
create table test_tbl ( id1 number, id2 number, id3 number, id4 number ) indexing on partition by range(id1) ( partition p1 values less than(1) indexing off, partition p2 values less than(2) indexing on, partition p3 values less than(3), partition p4 values less than(4), partition p5 values less than(5) );
The settings are visible in standard views in new column DEF_INDEXING, INDEXING
DBA|ALL|USER_PART_TABLES
select table_name, def_indexing from user_part_tables where table_name='TEST_TBL'; TABLE_NAME DEF_INDEXING ------------- ------------- TEST_TBL ON
and DBA|ALL|USER_TAB_PARTITIONS
select table_name, partition_name, indexing from user_tab_partitions where table_name='TEST_TBL' order by partition_position; TABLE_NAME PARTITION_NAME INDEXING ----------- -------------- -------- TEST_TBL P1 OFF TEST_TBL P2 ON TEST_TBL P3 ON TEST_TBL P4 ON TEST_TBL P5 ON
default indexing attribute for table can be change using ALTER TABLE command. It has impact only on new partitions.
alter table test_tbl modify default attributes indexing off; select table_name, def_indexing from user_part_tables where table_name='TEST_TBL'; TABLE_NAME DEF_INDEXING ------------- ------------- TEST_TBL OFF alter table test_tbl add partition p6 values less than(6); select table_name, partition_name, indexing from user_tab_partitions where table_name='TEST_TBL' order by partition_position; TABLE_NAME PARTITION_NAME INDEXING ----------- -------------- -------- TEST_TBL P1 OFF TEST_TBL P2 ON TEST_TBL P3 ON TEST_TBL P4 ON TEST_TBL P5 ON TEST_TBL P6 OFF
as well indexing attribute can be changed on partition level using ALTER TABLE command
alter table test_tbl modify partition p3 indexing off; select table_name, partition_name, indexing from user_tab_partitions where table_name='TEST_TBL' order by partition_position; TABLE_NAME PARTITION_NAME INDEXING ----------- -------------- -------- TEST_TBL P1 OFF TEST_TBL P2 ON TEST_TBL P3 OFF TEST_TBL P4 ON TEST_TBL P5 ON TEST_TBL P6 OFF
Now it’s time to create indexes on the table.New clause INDEXING PARTIAL for index creation reads attributes INDEXING for each partitions and create segments with status UNUSABLE for partitions with INDEXING=OFF.
create index test_tbl_idx_1 on test_tbl(id1) local indexing partial; select index_name, partition_name, status from user_ind_partitions where index_name='TEST_TBL_IDX_1' order by partition_position; INDEX_NAME PARTITION_NAME STATUS --------------- --------------- -------- TEST_TBL_IDX_1 P1 UNUSABLE TEST_TBL_IDX_1 P2 USABLE TEST_TBL_IDX_1 P3 UNUSABLE TEST_TBL_IDX_1 P4 USABLE TEST_TBL_IDX_1 P5 USABLE TEST_TBL_IDX_1 P6 UNUSABLE create index test_tbl_idx_2 on test_tbl(id2, id3) global partition by hash(id2) partitions 2 indexing partial; select index_name, partition_name, status from user_ind_partitions where index_name='TEST_TBL_IDX_2' order by partition_position; INDEX_NAME PARTITION_NAME STATUS --------------- --------------- -------- TEST_TBL_IDX_2 SYS_P444 USABLE TEST_TBL_IDX_2 SYS_P445 USABLE
If you change attribute on partition level to INDEXING OFF automatically corresponding partitions for local indexes becomes UNUSABLE
alter table test_tbl modify partition p4 indexing off; select index_name, partition_name, status from user_ind_partitions where index_name='TEST_TBL_IDX_1' order by partition_position; INDEX_NAME PARTITION_NAME STATUS --------------- --------------- -------- TEST_TBL_IDX_1 P1 UNUSABLE TEST_TBL_IDX_1 P2 USABLE TEST_TBL_IDX_1 P3 UNUSABLE TEST_TBL_IDX_1 P4 UNUSABLE TEST_TBL_IDX_1 P5 USABLE TEST_TBL_IDX_1 P6 UNUSABLE
and vice versa INDEXING ON will automatically rebuild partitions for partial indexes
alter table test_tbl modify partition p4 indexing on; select index_name, partition_name, status from user_ind_partitions where index_name='TEST_TBL_IDX_1' order by partition_position; INDEX_NAME PARTITION_NAME STATUS --------------- --------------- -------- TEST_TBL_IDX_1 P1 UNUSABLE TEST_TBL_IDX_1 P2 USABLE TEST_TBL_IDX_1 P3 UNUSABLE TEST_TBL_IDX_1 P4 USABLE TEST_TBL_IDX_1 P5 USABLE TEST_TBL_IDX_1 P6 UNUSABLE
Global indexes are maintained automatically during switching INDEXING attribute
select index_name, partition_name, status from user_ind_partitions where index_name='TEST_TBL_IDX_2' order by partition_position; INDEX_NAME PARTITION_NAME STATUS --------------- --------------- -------- TEST_TBL_IDX_2 SYS_P422 USABLE TEST_TBL_IDX_2 SYS_P422 USABLE
Creation of indexes with clause INDEXING FULL will ignore partition attribute INDEXING. In fact INDEXING FULL can be skipped it’s default value during creation of new index.
create index test_tbl_idx_3 on test_tbl(id2) local indexing full; create index test_tbl_idx_4 on test_tbl(id3) local; select index_name, partition_name, status from user_ind_partitions where index_name='TEST_TBL_IDX_3' order by partition_position; INDEX_NAME PARTITION_NAME STATUS --------------- --------------- -------- TEST_TBL_IDX_3 P1 USABLE TEST_TBL_IDX_3 P2 USABLE TEST_TBL_IDX_3 P3 USABLE TEST_TBL_IDX_3 P4 USABLE TEST_TBL_IDX_3 P5 USABLE TEST_TBL_IDX_3 P6 USABLE select index_name, partition_name, status from user_ind_partitions where index_name='TEST_TBL_IDX_4' order by partition_position; INDEX_NAME PARTITION_NAME STATUS --------------- --------------- -------- TEST_TBL_IDX_4 P1 USABLE TEST_TBL_IDX_4 P2 USABLE TEST_TBL_IDX_4 P3 USABLE TEST_TBL_IDX_4 P4 USABLE TEST_TBL_IDX_4 P5 USABLE TEST_TBL_IDX_4 P6 USABLE
Restrictions on Partial Indexes:
- The underlying table of a partial index cannot be a nonpartitioned table.
- Unique indexes cannot be partial indexes. This applies to indexes created with the CREATE UNIQUE INDEX statement and indexes that are implicitly created when you specify a unique constraint on one or more columns
Have a fun 🙂
Tomasz
Hi,
Very good post!
I like it very much.
🙂 Thanks
Is it same as –making local index partittion or subpartiton– unusable
ie
ALTER INDEX indexName MODIFY subPARTITION local_subpartition_indexname UNUSABLE
if they are not same
how making index unusable differs than INDEXING PARTIAL
Generally there is no difference here PARTIAL and UNUSABLE in effect will give the same but it’s easier to manage indexes using INDEXING PARTIAL than playing with UNUSABLE and REBUILD manually. Simple example switching from INDEXING OFF to INDEXING ON automatically rebuild partitions with UNUSABLE and vice versa. It’s huge benefit easier management, easier syntax.