With release 12c Oracle has introduced partial indexing, the possibility to define partial local indexes on partitioned table. This is an interesting feature that allows to save some space getting rid of index segments for old partitions. Let’s quickly see how this works.
First of all, when creating a partitioned table, the INDEXING property can be specified to define if we want or not to create the indexes for that partition (default value is ON unless a different default is set at table level)
|
1 2 3 4 5 6 7 8 9 |
drop table MYTAB; create table MYTAB(id number(10), run_date date, run_incr number(5)) partition by range(run_date) (partition P1 values less than (date '2016-01-02') INDEXING OFF, partition P2 values less than (date '2016-01-03'), partition P3 values less than (date '2016-01-04') ); |
This property can also be changed in a second time issuing a simple alter command
|
1 |
alter table MYTAB move partition P1 indexing on; |
The local indexes need to be created with the option INDEXING PARTIAL otherwise they will not be affected by the INDEXING ON/OFF property set at partition/subpartition level.
There is a restriction though: local unique indexes cannot be created as partial so it’s not possible to drop partitions of a unique index. Well, this is at least what Oracle says but let’s go on.