12c Database : Multiple indexes on same columns or column list

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.