Oracle Database 12c R1 New Features - Multiple Indexes on the same column or set of columns

Oracle Database 12c R1 New Features - Multiple Indexes on the same column or set of columns

In DB releases prior to Oracle DB 12c, you could not create multiple indexes either on the same column or set of columns in any form. 

But now in DB 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.

Example:


SQL> CREATE TABLE EMP_RECORDS
  2  (EMP_NO number (5),
  3  EMP_NAME varchar2 (30),
  4  EMP_SAL number (6) INVISIBLE)
  5  /

SQL> alter session set optimizer_use_invisible_indexes = true;

Session altered.

SQL> CREATE INDEX EMP_IND1 ON EMP_RECORDS (EMP_NO, EMP_NAME);

Index created.

SQL> CREATE BITMAP INDEX EMP_IND2 ON EMP_RECORDS (EMP_NO, EMP_NAME) INVISIBLE;

Index created.

Related:

Oracle Database 12c R1 New Features - Invisible columns

Installing Oracle Database 12c R1 (12.1.0.1.0)