Oracle 12c Multiple Indexes On Same Set of Columns

Oracle database 12c has about 500+ new features when compared to 11gR2. Among these features there will be some which get the mainstream attention. But often little caveats or enhancements prove to be as vital as the big ones. One such enhancement is the ability to have multiple indexes on same column or same set of columns. Lets look at this feature and the possible uses cases.

Creating Oracle 12c Database Multiple Indexes On Same Column

The creation of an index over a column or set of columns which have already been indexed is simple. You just have to ensure one thing and i.e. only one index is visible at any time. If already created index is visible then you will have to use the invisible keyword to make sure that newly created index is invisible. For example we have a table T1 in which C1 column is Primary Key and hence this column is already indexed.

SQL> select index_name from user_indexes where table_name='T1';
INDEX_NAME
----------------------------------------
SYS_C009869

Now if you try to create another index on Column C1 then you will get the error message below.

SQL> create index idx_t1 on t1(c1);
create index idx_t1 on t1(c1)
*
ERROR at line 1:
ORA-01408: such column list already indexed

However the following command will work and will create an additional index on the same C1 column. This was not possible until 11gR2.

SQL> create index idx_t1 on t1(c1) invisible;
Index created.

If you want you can go ahead and create another index, this time let it be a bitmap index.

SQL> create bitmap index idx_c1_bm on t1(c1) invisible;
Index created.

SQL> select index_name from user_indexes where table_name='T1';
INDEX_NAME
----------------------------------------
IDX_T1
IDX_C1_BM
SYS_C009869

Use Cases

Although you can now create more than one indexes on the same set of attributes but why would you want to do that. I mean creating an index takes time, space and also has operational overhead. Here are a couple of very good use cases in which you can use this new feature of a 12c database.

Testing Index Performance

The first use case can be to test the performance of newly created index. Suppose you already have a b-tree index on some columns and you want to test whether creating bitmap index on same columns can improve performance or not. You can create another bitmap index on same column just like we did above.

Once created you can now set the following session parameter to make invisible indexes visible to Optimizer.

SQL> alter session set optimizer_use_invisible_indexes=true;
Session altered.

Once this is done you can now test the performance of your new index although it will still be invisible. Once tested, you can now decide whether to keep b-tree index or the bitmap index. You can go ahead and drop either one of them.

Index Availability

The second not so obvious use case is to ensure that indexes remain available during operations like converting bitmap index to b-tree, local index to global in partitioned table, converting normal index to reverse key index and also replacing the index being used by some Primary Key constraint. All of these operations required indexes to remain unavailable to some specific period of time.

For example if you want to change a certain index but that index is being used by some Primary constraint then you will first have to disable the constraint, drop the index, rebuild it and finally enable the constraint again. Now the rebuilding process can take a lot of time especially if your data size is huge. And during all that time your table will be out of any Primary Key and an index on ID column. This can be very serious and can cause the denial of service attack on your database.

However with this multiple index feature you can now get rid of this downtime caused by rebuilding of index. You can create an index beforehand and mark it invisible. Like in our example above the IDX_T1 is on same identity column of T1 and is invisible. However the Primary Key is using the other index i.e. SYS_C009869.

Now if you want to change this then you can execute the following commands. All commands will take hardly second or two.

SQL> alter table t1 drop primary key;
Table altered.

SQL> drop index SYS_C009869;
Index dropped.

SQL> alter table t1 add constraint t1_pk primary key (c1);
Table altered.

Thus you have  successfully changed the Primary Key index and table was without any index for hardly a couple of seconds.

Oracle 12c via Multiple Column Indexes feature thus allows you to have a lot of flexibility in managing indexes and saves a lot of downtime.

Read more on Oracle 12c Database Default Column Value Enhancement.