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
