Oracle 12c New Features " Multiple indexes On The Same Column "

This feature can be particularly useful when you want to change the index definition in some way but you want to reduce the time in which the corresponding columns might not be indexed.

Pre Oracle 12c, you can’t create multiple indexes either on the same column or set of columns in any form. For example, if you have an index on column {a} or columns {a,b}, you can’t create another index on the same column or set of columns in the same order. In 12c, you can have multiple indexes on the same column or set of columns as long as the index type is different. However, only one type of index is usable/visible at a given time. In order to test the invisible indexes, you need to set the 

optimizer_use_use_invisible_indexes=true.

Here’s the example:

SQL> CREATE INDEX emp_ind1 ON EMP(ENO,ENAME);

SQL> CREATE BITMAP INDEX emp_ind2 ON EMP(ENO,ENAME) INVISIBLE;