Oracle 12c New Feature – Multiple Indexes On The Same Column Or Set of Columns

Starting with Oracle 12c, multiple indexes on same column or same set of columns can be created, as long as only one index is visible, and the indexes are physically different. It is not possible to create two bitmap indexes on the same column or set of columns. Example: Create a table ind_test1 SQL> create table ind_test1(id number, dept  varchar(20), start_dt date); Table created. Insert rows SQL>  insert into ind_test1 values(111, ‘TEST’, to_date(’01/01/2000′,’mm/dd/yyyy’)); 1 row created. SQL>  insert into ind_test1 values(222, ‘DEVL’,  to_date(’10/25/2011′,’mm/dd/yyyy’)); 1 row created. SQL>  insert into ind_test1 values(333, ‘TEST’,to_date(’05/22/1999′,’mm/dd/yyyy’)); 1 row created. Select * from ind_test1 table SQL> select * from ind_test1; ID DEPT                 START_DT ———- ——————– ——— 111 TEST                 01-JAN-00 222 DEVL                 25-OCT-11 333 TEST                 22-MAY-99     Create the first index on start_dt column. By default, Oracle creates B-tree index. SQL>  create index idx1_ind_test1 on ind_test1(start_dt); Index created.   Now, create a second invisible index on the same column SQL> create index idx2_ind_test1 on ind_test1(start_dt) invisible; create index idx2_ind_test1 on ind_test1(start_dt) invisible * ERROR at line 1: ORA-01408: such column list already indexed   Index failed because these 2 are the same type of indexes. You can create a bitmap index on the same column. SQL>  (more…)