From 12c onwards, we can create a mixed indexes (bitmap and btree both) on same columns which is very useful in DW environments. Before to 12c you will receive the such column is already indexed error or index already exists if you want to create the same.
I have a copy of dba_objects as below
SQL> desc t Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(128) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(128) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(23) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER EDITION_NAME VARCHAR2(128) SHARING VARCHAR2(13) EDITIONABLE VARCHAR2(1) ORACLE_MAINTAINED VARCHAR2(1)
Create an btree index
SQL> create index t1 on sys.t(object_name); Index created.
Lets try to create another index t2,
SQL> create index t2 on sys.t(object_name); create index t2 on sys.t(object_name) * ERROR at line 1: ORA-01408: such column list already indexed
Hu Hu, no where possible, Lets mark this index invisible;
SQL> alter index t1 invisible; Index altered. SQL> create index t2 on sys.t(object_name); create index t2 on sys.t(object_name) * ERROR at line 1: ORA-01408: such column list already indexed
Still the same when I am trying to create the btree index, Let's try to create the bitmap index
SQL> create bitmap index t2 on sys.t(object_name); Index created.
Whoa, bitmap index created on same column which already contains btree index. So it means you can have combination of bitmap or btree indexes on same columns whilst one should be invisble. Ofcourse if you want to use the invisible index just set the parameter optimizer_use_invisible_indexes to true will use both indexes by optimizer.
Follow Me!!!