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.
Our test table has INDEXING set to OFF on the first partition, let’s populate it with some data executing
|
1 2 3 4 5 6 7 8 9 |
insert into MYTAB with generator as (select null from dual connect by level <= 10000) select rownum id, case round(dbms_random.value(1,3)) when 1 then date '2016-01-01' when 2 then date '2016-01-02' when 3 then date '2016-01-03' end run_date, round(dbms_random.value(1, 10000)) run_incr from generator; commit; |
Then let’s create two indexes
|
1 2 |
create index MYTAB_IDX on MYTAB(RUN_INCR) local indexing partial; create unique index MYTAB_IDX_UQ on MYTAB(RUN_DATE, ID) local; |
As you can see we specified INDEXING PARTIAL only on the non unique index. Just to finish our test case let’s grab some statistics
|
1 |
exec dbms_stats.gather_table_stats(user, 'MYTAB'); |
Now let’s check what’s the current status of our partitions
|
1 2 3 4 5 6 7 8 9 10 |
SQL> select table_name, partition_name, indexing 2 from user_tab_partitions 3 where table_name = 'MYTAB' 4 order by partition_name; TABLE_NAME PARTITION_ INDEXING ---------- ---------- -------- MYTAB P1 OFF MYTAB P2 ON MYTAB P3 ON |
As you can see in the %_TAB_PARTITIONS and %_TAB_SUBPARTITIONS views of Oracle 12c there is a new column showing the INDEXING property.
When checking the index segments
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> select uip.index_name, uip.partition_name, ui.indexing, uip.status 2 from user_indexes ui, user_ind_partitions uip 3 where ui.table_name = 'MYTAB' 4 and ui.index_name = uip.index_name 5 order by uip.partition_name, ui.index_name; INDEX_NAME PARTITION_ INDEXING STATUS --------------- ---------- -------- -------- MYTAB_IDX P1 PARTIAL UNUSABLE MYTAB_IDX_UQ P1 FULL USABLE MYTAB_IDX P2 PARTIAL USABLE MYTAB_IDX_UQ P2 FULL USABLE MYTAB_IDX P3 PARTIAL USABLE MYTAB_IDX_UQ P3 FULL USABLE |
we can see that, as expected, the index partition on P1 has not been created for the partial index MYTAB_IDX. We can also verify that there is no segment created or any space allocated for that index partition
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> select ui.table_name tab, uip.partition_name part, ui.index_name, nvl(trim(to_char(us.bytes, '999,999,999,999,999')), 0) indsize 2 from user_indexes ui, user_part_indexes upi, user_ind_partitions uip, user_segments us 3 where ui.index_name = upi.index_name 4 and upi.index_name = uip.index_name 5 and uip.index_name = us.segment_name (+) 6 and uip.partition_name = us.partition_name (+) 7 and us.segment_type (+) = 'INDEX PARTITION' 8 and ui.table_name = 'MYTAB' 9 order by uip.partition_name, ui.index_name; TAB PART INDEX_NAME INDSIZE ---------- ---------- --------------- -------------------- MYTAB P1 MYTAB_IDX 0 MYTAB P1 MYTAB_IDX_UQ 8,519,680 MYTAB P2 MYTAB_IDX 8,519,680 MYTAB P2 MYTAB_IDX_UQ 8,585,216 MYTAB P3 MYTAB_IDX 8,519,680 MYTAB P3 MYTAB_IDX_UQ 8,519,680 |
But what if we want to get rid of old partitions for unique indexes? Is it really not possible?
If we drop and try to create a unique index with the INDEXING PARTIAL option we get an error with a clear description that leaves no doubts about it
|
1 2 3 4 5 6 |
SQL> drop index MYTAB_IDX_UQ; Index dropped SQL> create unique index MYTAB_IDX_UQ on MYTAB(RUN_DATE, ID) local indexing partial; create unique index MYTAB_IDX_UQ on MYTAB(RUN_DATE, ID) local indexing partial ORA-14226: unique index may not be PARTIAL |
In the first moment I thought “Well, it makes sense, how can Oracle grant uniqueness on a table if we remove a part of the unique index?”.
But wait! When I create a unique index, Oracle forces me to include in it all the columns used in the partitioning schema
|
1 2 3 |
SQL> create unique index MYTAB_IDX_UQ on MYTAB(ID) local; create unique index MYTAB_IDX_UQ on MYTAB(ID) local ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index |
This means that there is no way I can have duplicate values across different partitions. So why doesn’t Oracle allow partial indexing on a unique local index?
Let’s create our unique local index without the partial option
|
1 2 |
SQL> create unique index MYTAB_IDX_UQ on MYTAB(RUN_DATE, ID) local; Index created |
We are now back to the initial configuration where MYTAB_IDX has no partition for P1 while the one for the unique index MYTAB_IDX_UQ exists.
Now we can use a small trick: we know that any “alter table … move partition …” command make the indexes unusable for that partition. Yes, also the unique ones!
So let’s execute
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> alter table MYTAB move partition P1; Table altered SQL> select uip.index_name, uip.partition_name, ui.indexing, uip.status 2 from user_indexes ui, user_ind_partitions uip 3 where ui.table_name = 'MYTAB' 4 and ui.index_name = uip.index_name 5 order by uip.partition_name, ui.index_name; INDEX_NAME PARTITION_ INDEXING STATUS --------------- ---------- -------- -------- MYTAB_IDX P1 PARTIAL UNUSABLE MYTAB_IDX_UQ P1 FULL UNUSABLE MYTAB_IDX P2 PARTIAL USABLE MYTAB_IDX_UQ P2 FULL USABLE MYTAB_IDX P3 PARTIAL USABLE MYTAB_IDX_UQ P3 FULL USABLE |
Bingo! Because the move command affects all indexes on the partition, now also the unique index is unusable and it’s basically behaving like it was PARTIAL and not FULL.
Let’s verify if there is any space allocated for it
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> select ui.table_name tab, uip.partition_name part, ui.index_name, nvl(trim(to_char(us.bytes, '999,999,999,999,999')), 0) indsize 2 from user_indexes ui, user_part_indexes upi, user_ind_partitions uip, user_segments us 3 where ui.index_name = upi.index_name 4 and upi.index_name = uip.index_name 5 and uip.index_name = us.segment_name (+) 6 and uip.partition_name = us.partition_name (+) 7 and us.segment_type (+) = 'INDEX PARTITION' 8 and ui.table_name = 'MYTAB' 9 order by uip.partition_name, ui.index_name; TAB PART INDEX_NAME INDSIZE ---------- ---------- --------------- -------------------- MYTAB P1 MYTAB_IDX 0 MYTAB P1 MYTAB_IDX_UQ 0 MYTAB P2 MYTAB_IDX 8,519,680 MYTAB P2 MYTAB_IDX_UQ 8,585,216 MYTAB P3 MYTAB_IDX 8,519,680 MYTAB P3 MYTAB_IDX_UQ 8,519,680 |
0 bytes for all indexes on partition P1! With this small workaround we managed to get rid of all index partitions on P1 regardless of the index being unique or not.
But what happens to the table in this condition? Well, not really anything to be worried about. Select statements on P1 still work without any problem and same updates
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> select * from MYTAB partition (P1) where rownum <= 3; ID RUN_DATE RUN_INCR ----------- ----------- -------- 3 01/01/2016 5660 10 01/01/2016 8878 26 01/01/2016 4233 SQL> update MYTAB set RUN_INCR = 0 where ID = 3 and RUN_DATE = date '2016-01-01'; 1 row updated SQL> select * from MYTAB where ID = 3 and RUN_DATE = date '2016-01-01'; ID RUN_DATE RUN_INCR ----------- ----------- -------- 3 01/01/2016 0 |
The only problem is that insert statements fail because the unique index is in “UNUSABLE” status
|
1 2 3 |
SQL> insert into MYTAB(ID, RUN_DATE, RUN_INCR) values(1, date '2016-01-01', 999); insert into MYTAB(ID, RUN_DATE, RUN_INCR) values(1, date '2016-01-01', 999) ORA-01502: index 'SCOTT.MYTAB_IDX_UQ' or partition of such index is in unusable state |
Assuming we only want to drop indexes for partitions that contain old historical data and that we are not going to modify anymore, this is not a big limitation at all.
An interesting aspect is that uniqueness is still guaranteed on the rest of the table as a proof that we are not breaking anything here
|
1 2 3 4 5 6 7 8 9 10 |
SQL> select * from MYTAB partition (P2) where rownum <= 3; ID RUN_DATE RUN_INCR ----------- ----------- -------- 6741 02/01/2016 9048 6744 02/01/2016 4235 6745 02/01/2016 9567 SQL> insert into MYTAB(ID, RUN_DATE, RUN_INCR) values(6741, date '2016-01-02', 999); insert into MYTAB(ID, RUN_DATE, RUN_INCR) values(6741, date '2016-01-02', 999) ORA-00001: unique constraint (SCOTT.MYTAB_IDX_UQ) violated |
But now it’s time to check how the CBO deal with our indexes
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SQL> set autotrace traceonly explain SQL> set lines 300 SQL> select * 2 from mytab 3 where run_incr = 6741; Execution Plan ---------------------------------------------------------- Plan hash value: 3718852822 -------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 32 | 279 (0)| 00:00:01 | | | | 1 | VIEW | VW_TE_2 | 2 | 70 | 279 (0)| 00:00:01 | | | | 2 | UNION-ALL | | | | | | | | | 3 | PARTITION RANGE ITERATOR | | 1 | 16 | 5 (0)| 00:00:01 | 2 | 3 | | 4 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| MYTAB | 1 | 16 | 5 (0)| 00:00:01 | 2 | 3 | |* 5 | INDEX RANGE SCAN | MYTAB_IDX | 2 | | 3 (0)| 00:00:01 | 2 | 3 | | 6 | PARTITION RANGE SINGLE | | 1 | 16 | 274 (0)| 00:00:01 | 1 | 1 | |* 7 | TABLE ACCESS FULL | MYTAB | 1 | 16 | 274 (0)| 00:00:01 | 1 | 1 | -------------------------------------------------------------------------------------------------------------------------- |
This query accesses the column on which we created the non unique partial index. As you can see the CBO is smart enough to split the operation in two different chunks: one accesses the partitions that have INDEXING ON using the index, the other one executes a full table scan on the partition that has no index.
If we run a similar query but trying to use the unique index
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SQL> select * 2 from mytab 3 where id = 6741; Execution Plan ---------------------------------------------------------- Plan hash value: 1517594737 ----------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 16 | 279 (0)| 00:00:01 | | | | 1 | VIEW | VW_TE_2 | 2 | 70 | 279 (0)| 00:00:01 | | | | 2 | UNION-ALL | | | | | | | | | 3 | PARTITION RANGE ITERATOR | | 1 | 16 | 5 (0)| 00:00:01 | 2 | 3 | | 4 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| MYTAB | 1 | 16 | 5 (0)| 00:00:01 | 2 | 3 | |* 5 | INDEX SKIP SCAN | MYTAB_IDX_UQ | 1 | | 4 (0)| 00:00:01 | 2 | 3 | | 6 | PARTITION RANGE SINGLE | | 1 | 16 | 274 (0)| 00:00:01 | 1 | 1 | |* 7 | TABLE ACCESS FULL | MYTAB | 1 | 16 | 274 (0)| 00:00:01 | 1 | 1 | ----------------------------------------------------------------------------------------------------------------------------- |
we get the same result. This shows how the partial UNIQUE index is behaving exactly as the other non unique one and the CBO uses the index wherever this is usable.
Despite the limitation that Oracle sets, applying partial indexing to unique indexes is possible and doesn’t seem to cause any trouble so it might a viable solution to reduce the database footprint.
Only thing to take into consideration is that, as shown during this demo, unique index should be dropped only for old partitions where new rows are not likely to be inserted anymore.
