PARTIAL Indexes
Oracle PARTIAL INDEXES – a new feature in Oracle 12c, initial impression was really good, but the implementation is quite poor. With this feature, you can define INDEXING OFF for a partition so that any index with PARTIAL clause will skip this partition while creating the index. The advantage is you can selectively create indexes on partitions. And for a global index or a global partitioned index, the rows from the INDEXING OFF partitions will be skipped while creating the index.
You need to create the partitions with INDEXING OFF while creating the table to opt PARTIAL INDEXES
create table test (C1 number,c2 varchar2(2))
partition by list(c2) (
partition p1 values('A') INDEXING OFF,
partition p2 values('B') INDEXING OFF,
partition p3 values('C') INDEXING OFF,
partition p4 values('D'));
There are 3 partitions with INDEXING OFF, while the partition P4 will get index by default.
SQL> select PARTITION_NAME,INDEXING from user_tab_partitions ; PARTITION_NAME INDE --------------- ---- P1 OFF P2 OFF P3 OFF P4 ON
Now, I have created indexes on both columns – two indexes.
SQL> create index i_c1 on test(c1) LOCAL indexing PARTIAL; Index created. SQL> create index i_c2 on test(c2) LOCAL indexing PARTIAL;SQL> Index created. SQL> select TABLE_NAME,INDEX_NAME from user_indexes; TABLE_NAME INDEX_NAME --------------- --------------- TEST I_C1 SQL> select PARTITION_NAME,INDEX_NAME,status from user_ind_partitions ; PARTITION_NAME INDEX_NAME STATUS --------------- --------------- -------- P1 I_C1 UNUSABLE P2 I_C1 UNUSABLE P3 I_C1 UNUSABLE P4 I_C1 USABLE P1 I_C2 UNUSABLE P2 I_C2 UNUSABLE P3 I_C2 UNUSABLE P4 I_C2 USABLE
My expectation was it will create two indexes on column P4, but Oracle created UNUSABLE indexes on rest of the columns where INDEXING is OFF. Not really impressed on the fact that, I have to keep UNUSABLES indexes in my production databases, just because of I choose the new feature, PARTIAL indexes! This is challenge to identify the difference between real UNUSABLE indexes and UNUSABLE indexes due to PARTIAL indexes. Well, Oracle should have avoided creating these unwanted objects if this is a serious implementation.
Now I re-built all the UNUSABLE indexes – just want to know how Oracle implemented this feature – it is going to stop me while re-building the indexes, because the option INDEXING OFF?
SQL> alter index i_c1 rebuild partition p1 tablespace users; Index altered. SQL> alter index i_c1 rebuild partition p2 tablespace users; Index altered. SQL> alter index i_c1 rebuild partition p3 tablespace users; Index altered. SQL> alter index i_c1 rebuild partition p4 tablespace users; Index altered. SQL> alter index i_c2 rebuild partition p4 tablespace users; Index altered. SQL> alter index i_c2 rebuild partition p3 tablespace users; Index altered. SQL> alter index i_c2 rebuild partition p2 tablespace users; Index altered. SQL> alter index i_c2 rebuild partition p1 tablespace users; Index altered.
Now all of my PARTIAL indexes are rebuilt and USABLE, but still, partitions say they are PARTIAL!
SQL> select PARTITION_NAME,INDEX_NAME,status,tablespace_name from user_ind_partitions ; PARTITION_NAME INDEX_NAME STATUS TABLESPACE_NAME --------------- --------------- -------- ------------------------------ P4 I_C2 USABLE USERS P3 I_C2 USABLE USERS P2 I_C2 USABLE USERS P1 I_C2 USABLE USERS P4 I_C1 USABLE USERS P3 I_C1 USABLE USERS P2 I_C1 USABLE USERS P1 I_C1 USABLE USERS 8 rows selected. SQL> select index_nAme,INDEXING from user_indexes; INDEX_NAME INDEXIN --------------- ------- I_C1 PARTIAL I_C2 PARTIAL
Is this something new in Oracle 12c? I dropped and re-created the index again with PARTIAL option.
SQL> drop index I_C1; Index dropped. SQL> drop index I_C2 ; Index dropped. SQL> create index i_c1 on test(c1) LOCAL indexing PARTIAL; Index created. SQL> create index i_c2 on test(c2) LOCAL indexing PARTIAL; Index created. SQL> select index_name,INDEXING from user_indexes; INDEX_NAME INDEXIN --------------- ------- I_C2 PARTIAL I_C1 PARTIAL SQL> select PARTITION_NAME,INDEX_NAME,status from user_ind_partitions ; PARTITION_NAME INDEX_NAME STATUS --------------- --------------- -------- P1 I_C1 UNUSABLE P2 I_C1 UNUSABLE P3 I_C1 UNUSABLE P4 I_C1 USABLE P1 I_C2 UNUSABLE P2 I_C2 UNUSABLE P3 I_C2 UNUSABLE P4 I_C2 USABLE 8 rows selected.
Since the index partitions are UNUSABLE, I was very curious to know what will happen if I set SKIP_UNUSABLE_INDEXES to FALSE, while thedefault value is TRUE.
SQL> alter session set skip_unusable_indexes =false; Session altered. SQL> insert into test select rownum n,'B' from all_objects where rownum < 10; insert into test select rownum n,'B' from all_objects where rownum < 10 * ERROR at line 1: ORA-01502: index 'USR1.I_C2' or partition of such index is in unusable state
As expected the PARTIAL indexes started failing with the error “ORA-01502: index ‘USR1.I_C2’ or partition of such index is in unusable state”! So, PARTIAL indexes is just a UNUSABLE LOCAL index which can implement in 11g as well!
However, INDEXING OFF got some good effect on GLOBAL indexes – partitioned or non-partitioned. Global indexes, indeed skip the rows from the INDEXING OFF partitions.
SQL> create index i_c1 on test(c1) ; Index created. SQL> select SEGMENT_NAME,BYTES,PARTITION_NAME from user_segments where SEGMENT_NAME='I_C1'; SEGMENT_NAME BYTES PARTITION_NAME -------------------- ---------- --------------- I_C1 7340032 SQL> drop index i_c1 ; Index dropped. SQL> create index i_c1 on test(c1) indexing partial; Index created. SQL> select SEGMENT_NAME,BYTES,PARTITION_NAME from user_segments where SEGMENT_NAME='I_C1'; SEGMENT_NAME BYTES PARTITION_NAME -------------------- ---------- --------------- I_C1 2097152 SQL> alter index i_c1 rebuild ; Index altered. SQL> select SEGMENT_NAME,BYTES,PARTITION_NAME from user_segments where SEGMENT_NAME='I_C1'; SEGMENT_NAME BYTES PARTITION_NAME -------------------- ---------- --------------- I_C1 2097152
The first index with FULL option created an index with size 7340032 bytes while the second option with PARTIAL index created with 2097152 bytes. That is cool – Oracle is able to skip the rows from INDEXING OFF partitions while creating the global index!
Thomas Saviour's Blog 