Starting with 12c, Oracle supports creating different type of indexes on same column(s). MySQL supported this kind of indexing mechanism and glad that Oracle also supports it. Generally indexing the same column is considered  a performance bottleneck because it slows down inserts and updates to maintain multiple indexes but some times it becomes a necessity.

Few reasons why you would need different type of indexes on same of columns

1. B*TREE for OLTP sessions v.s BITMAP for nightly reports
2. Local vs Global Indexes
3. Testing  performance of different type of index

SQL> SELECT * FROM V$VERSION
2  ;

BANNER
——————————————————————————
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
PL/SQL Release 11.2.0.2.0 – Production
CORE    11.2.0.2.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 – Production
NLSRTL Version 11.2.0.2.0 – Production

SQL> CREATE TABLE EMP
2  (
3  EMPNO      NUMBER(9)       PRIMARY KEY ,
4  EMPNAME    VARCHAR2(15)    NOT NULL,
5  DEPTNO             NUMBER(4)       ,
6  PHONENO    NUMBER(10)      ,
7  HIREDATE   DATE            ,
8  SALARY     NUMBER(9, 2)
9  )
10  /

Table created.

SQL> CREATE INDEX EMP_DEPT_BTREE_IDX ON EMP(DEPTNO);

Index created.

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

Lets us repeat the same steps in Oracle12c

SQL> SELECT * FROM V$VERSION;

BANNER                                                                               CON_ID
——————————————————————————– ———-
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production              0
PL/SQL Release 12.1.0.1.0 – Production                                                    0
CORE    12.1.0.1.0      Production                                                                0
TNS for Linux: Version 12.1.0.1.0 – Production                                            0
NLSRTL Version 12.1.0.1.0 – Production                                                    0
SQL> CREATE TABLE EMP
2  (
3  EMPNO      NUMBER(9)       PRIMARY KEY ,
4  EMPNAME    VARCHAR2(15)    NOT NULL,
5  DEPTNO             NUMBER(4)       ,
6  PHONENO    NUMBER(10)      ,
7  HIREDATE   DATE            ,
8  SALARY     NUMBER(9, 2)
9  )
10  TABLESPACE USERS
11  ;

Table created.

SQL>
SQL>
SQL> CREATE INDEX EMP_DEPT_BTREE_IDX ON EMP(DEPTNO);

Index created.

SQL> CREATE BITMAP INDEX EMP_DEPTNO_BITMAP_IDX ON EMP(DEPTNO) INVISIBLE  ;

Index created.

.

Tagged: multiple index on same columns, multiple indexes on same column, Oracle12 index