Oracle 12C multi-index with the same column

Oracle 12C multi-index with the same column

In Oracle 12c, Oracle provides the ability to create different indexes on the columns of the same table for SQL Performance Optimization, but only one index is visible.

The following is an example
Connected:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter session set container = pdb1;

Session altered.

SQL> CREATE TABLE t (
2 id NUMBER,
3 ename VARCHAR2 (50)
4 );

Table created.

SQL> CREATE INDEX t_idx1 ON t (ename) VISIBLE;

Index created.

SQL> CREATE INDEX t_idx2 ON t (ename) INVISIBLE;
Create index t_idx2 ON t (ename) INVISIBLE
*
ERROR at line 1:
ORA-01408: such column list already indexed

 

SQL> CREATE BITMAP INDEX t_idx3 ON t (ename) INVISIBLE;

Index created.
SQL> l
1 SELECT a. index_name,
2 a. index_type,
3 a. partitioned,
4 B. partitioning_type,
5 B. locality,
6 a. visibility
7 FROM user_indexes
8 left outer join user_part_indexes B ON a. index_name = B. index_name where a. table_name = 'T'
9 * order by index_name
SQL>/

INDEX_NAME INDEX_TYPE PARTITIONED PARTITIONING_TYPE LOCALITY VISIBILITY
-----------------------------------------------------------------------------
T_IDX1 NORMAL NO VISIBLE
T_IDX3 BITMAP NO INVISIBLE