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> create bitmap index idx2_ind_test1 on ind_test1(start_dt) invisible; Index created.
This feature is useful when you want to test the performance of different indexes without dropping the existing one. If you already have a b-tree index on a set of columns and you want to test whether creating bitmap index on the same set of columns will improve performance, you can make the existing one invisible and create a new visible one and perform the test.
This is also useful if you want to quickly migrate to a different index type.