Thursday, June 27, 2013

Oracle 12c New Features - multiple indexes on the same set of columns

With Oracle 12c it is now possible to have multiple indexes on the same set of columns as long as there is a difference between the indexes (index type, partitioning etc), and one is invisible.

This makes it possible to quickly change index strategies with minimum impact to applications.

For this example I will first create a test partitioned table.

SQL> CREATE TABLE test_range
(id  NUMBER(5),
att1 char(1),
att2 char(1),
att3 char(1))
PARTITION BY RANGE(id)
(
PARTITION id_10 VALUES LESS THAN(10),
PARTITION id_20 VALUES LESS THAN(20)
);  

Table created.

Now create a global index on ATT1.

SQL> create index att1_global on test_range(att1) global;

Index created.

Try to create another index on ATT1 with local partitioning, and it fails because the existing index is still visible.

SQL> create index att1_local on test_range(att1) local;
create index att1_local on test_range(att1) local
                                      *
ERROR at line 1:
ORA-01408: such column list already indexed

Try to create an invisible global index on ATT1 and it fails because the structure is the same as the existing index.

SQL> create index att1_global2 on test_range(att1) global invisible;
create index att1_global2 on test_range(att1) global invisible
                                        *
ERROR at line 1:
ORA-01408: such column list already indexed

Try to create an invisible index with local partitioning and it is successful.

SQL> create index att1_local on test_range(att1) local invisible;

Index created.

Try to make the locally partitioned index visible, and it fails because there is another visible index with the same columns.

SQL> alter index att1_local visible;
alter index att1_local visible
*
ERROR at line 1:
ORA-14147: There is an existing VISIBLE index defined on the same set of
columns.

We need to make the existing index invisible first, then make the new index visible.  With this method we can easily test multiple indexing strategies without needing long outages to drop and recreate indexes.

SQL> alter index att1_global invisible;

Index altered.

SQL> alter index att1_local visible;

Index altered.