Creating Multiple Indexes on the same column or set of columns

Introduction

Oracle 11g Introduced a nice feature named "Invisible Indexes" which allows us to mark an index as invisible. This index will still be maintained by Oracle during DML operations like a "regular" index but it is being ignored by the optimizer, i.e. execution plans will not use the invisible indexes.

Oracle 12c leverages the "Invisible Indexes" feature by allowing us create multiple indexes on the same column or set of columns, as long as only one index is visible and all the indexes are different, i.e. it is impossible to create 2 B-Tree indexes on the same column, even if one of them is invisible.

Demonstration

For this demonstration, we will create a table of employees and populate it with 10 records:

SQL> CREATE TABLE EMP
  2  (
  3     id          NUMBER,
  4     name        VARCHAR2 (20),
  5     salary      NUMBER,
  6     hire_date   DATE
  8  );
Table created.

SQL> insert into EMP value values (1, 'DAVID', 8500, '07-APR-15');
1 row created.

SQL> insert into EMP value values (2, 'JOHN', 10000, '17-MAR-15');
1 row created.

SQL> insert into EMP value values (3, 'JANE', 13500, '23-DEC-15');
1 row created.

SQL> insert into EMP value values (4, 'DAN', 15000, '02-JAN-15');
1 row created.

SQL> insert into EMP value values (5, 'RACHEL', 19000, '20-FEB-15');
1 row created.

SQL> insert into EMP value values (6, 'BRAD', 20000, '25-JUN-15');
1 row created.

SQL> insert into EMP value values (7, 'TIM', 15000, '16-MAR-15');
1 row created.

SQL> insert into EMP value values (8, 'KELLY', 9000, '28-APR-15');
1 row created.

SQL> insert into EMP value values (9, 'NICK', 7500, '04-FEB-15');
1 row created.

SQL> insert into EMP value values (10, 'ERIK', 6000, '09-JUL-15');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from EMP;
        ID NAME                     SALARY HIRE_DATE
---------- -------------------- ---------- ---------
         1 DAVID                      8500 07-APR-15
         2 JOHN                      10000 17-MAR-15
         3 JANE                      13500 23-DEC-15
         4 DAN                       15000 02-JAN-15
         5 RACHEL                    19000 20-FEB-15
         6 BRAD                      20000 25-JUN-15
         7 TIM                       15000 16-MAR-15
         8 KELLY                      9000 28-APR-15
         9 NICK                       7500 04-FEB-15
        10 ERIK                       6000 09-JUL-15
10 rows selected.

Now, we will create 2 different indexes on the "id" column, the first index will be a visible B-Tree index, and the second index will be an invisible Bitmap index:

SQL> CREATE INDEX BTREE_IDX ON EMP (ID);
Index created.

SQL> CREATE BITMAP INDEX BITMAP_IDX ON EMP (ID);
CREATE BITMAP INDEX BITMAP_IDX ON EMP (ID)
                                       *
ERROR at line 1:
ORA-01408: such column list already indexed


SQL> CREATE BITMAP INDEX BITMAP_IDX ON EMP (ID) INVISIBLE;
Index created.

The error in the second statement is expected, because the B-Tree index is visible and as previously mentioned, only one index can be visible at a time. Using USER_INDEXES dictionary view, it is possible to determine the current status of the indexes:

select index_name, index_type, visibility
  from user_indexes
 where TABLE_NAME = 'EMP'

INDEX_NAME INDEX_TYPE                  VISIBILIT
---------- --------------------------- ---------
BTREE_IDX  NORMAL                      VISIBLE
BITMAP_IDX BITMAP                      INVISIBLE

If we want to test the performance of the SQL statements, or investigate the execution plans after switching between the indexes, it can be done very easily, as follows:

SQL> ALTER INDEX BTREE_IDX INVISIBLE;
Index altered.

SQL> ALTER INDEX BITMAP_IDX VISIBLE;
Index altered.

SQL> SELECT index_name, index_type, visibility
  FROM user_indexes
 WHERE TABLE_NAME = 'EMP';

INDEX_NAME INDEX_TYPE                  VISIBILIT
---------- --------------------------- ---------
BTREE_IDX  NORMAL                      INVISIBLE
BITMAP_IDX BITMAP                      VISIBLE

Summary

In this article I have demonstrated a new 12c feature that allows create more than one index on a column or set of columns, assuming the indexes are from different types.

This can be useful when you want to test the impact of different indexes easily without dropping and creating a new index. However, it is important to bear in mind that additional indexes means additional overhead during DML operations. This additional overhead is necessary in order to maintain the indexes, therefore use this feature with cautious