Similarly as discussed in this post explaining how to enable partial unique indexes on Oracle 12c, it is possible to use partial indexes on Oracle 11g (tested on 11.2.0.2.0) as well! As you know this feature has been introduced with the Oracle release 12c but using the same method explained in the previous post we can apply it to 11g.
Let’s create our usual test table
|
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'), partition P2 values less than (date '2016-01-03'), partition P3 values less than (date '2016-01-04') ); |
Here we can’t specify any INDEXING option because there is no such feature on 11g. Let’s also populate the table
|
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; |
and the local unique index
|
1 |
create unique index MYTAB_IDX_UQ on MYTAB(RUN_DATE, ID) local; |
Before proceeding let’s also gather stats
|
1 |
exec dbms_stats.gather_table_stats(user, 'MYTAB'); |
The index has been created on all partitions with USABLE status and it’s segments occupy some space
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SQL> select uip.index_name, uip.partition_name, 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_NAME STATUS ------------------------------ --------------- -------- MYTAB_IDX_UQ P1 USABLE MYTAB_IDX_UQ P2 USABLE MYTAB_IDX_UQ P3 USABLE 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_UQ 131,072 MYTAB P2 MYTAB_IDX_UQ 196,608 MYTAB P3 MYTAB_IDX_UQ 131,072 |
Now, let’s try to apply the same method we used for unique indexes on Oracle 12c and issue a “move partition” then check what happens
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
SQL> alter table MYTAB move partition P1; Table altered SQL> select uip.index_name, uip.partition_name, 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_NAME STATUS ------------------------------ --------------- -------- MYTAB_IDX_UQ P1 UNUSABLE MYTAB_IDX_UQ P2 USABLE MYTAB_IDX_UQ P3 USABLE 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_UQ 0 MYTAB P2 MYTAB_IDX_UQ 196,608 MYTAB P3 MYTAB_IDX_UQ 131,072 |
We are not surprised anymore to see that the index partition has been dropped with its segment freeing some space.
This sounds interesting but how does the CBO deals with this situation? On 12c, considering that the feature has been officially introduced by Oracle, the CBO is smart enough to “split” scans on different partition using indexes wherever they are available otherwise choosing a full scan. Here on 11g the behaviour is a bit different.
Let’s explain a first query that accesses only a partition on which the index exists
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SQL> select * 2 from mytab 3 where run_date = date '2016-01-02' 4 and id = 1; Execution Plan ---------------------------------------------------------- Plan hash value: 895179106 ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 16 | 2 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE | | 1 | 16 | 2 (0)| 00:00:01 | 2 | 2 | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID| MYTAB | 1 | 16 | 2 (0)| 00:00:01 | 2 | 2 | |* 3 | INDEX UNIQUE SCAN | MYTAB_IDX_UQ | 1 | | 1 (0)| 00:00:01 | 2 | 2 | ------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("RUN_DATE"=TO_DATE(' 2016-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "ID"=1) |
On the selected partition the index exists so Oracle uses it. Same happens if we access more partitions all with existing index
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SQL> select * 2 from mytab 3 where run_date > date '2016-01-02' 4 and id = 1; Execution Plan ---------------------------------------------------------- Plan hash value: 163191677 ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 16 | 3 (0)| 00:00:01 | | | | 1 | PARTITION RANGE ITERATOR | | 1 | 16 | 3 (0)| 00:00:01 | 2 | 3 | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID| MYTAB | 1 | 16 | 3 (0)| 00:00:01 | 2 | 3 | |* 3 | INDEX SKIP SCAN | MYTAB_IDX_UQ | 1 | | 2 (0)| 00:00:01 | 2 | 3 | ------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("RUN_DATE">TO_DATE(' 2016-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "ID"=1 AND "RUN_DATE" IS NOT NULL) filter("ID"=1) |
What happens if we remove the condition on the date and force the CBO to look into all the partitions? Let’s check
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SQL> select * 2 from mytab 3 where id = 1; Execution Plan ---------------------------------------------------------- Plan hash value: 2264946163 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 16 | 14 (0)| 00:00:01 | | | | 1 | VIEW | VW_TE_2 | 2 | 70 | 14 (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| 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 | 9 (0)| 00:00:01 | 1 | 1 | |* 7 | TABLE ACCESS FULL | MYTAB | 1 | 16 | 9 (0)| 00:00:01 | 1 | 1 | --------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("MYTAB"."RUN_DATE">=TO_DATE(' 2016-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "ID"=1 AND "MYTAB"."RUN_DATE" |
Well, surprise for me, also on 11g the CBO is smart enough to split the operation and use indexes wherever they exist!
Conclusion of this short demo is that on Oracle release 11g it is possible to remove old indexes partitions in order to reduce the database footprint. From the tests I’ve been running it seems there are no drawbacks in using this method but let me know if you come up with something.
