Archive

Posts Tagged ‘Oracle indexes’

PARTIAL Indexes

February 15, 2016 Leave a comment

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!

 

Advertisements