In Oracle 12C you can create multiple indexes on the same set of columns as long as some characteristic is different like:
- Unique versus nonunique
- B-tree versus bitmap
- Different partitioning strategies
- Indexes that are not partitioned and indexes that are partitioned
- Indexes that are locally partitioned and indexes that are globally partitioned
- Indexes that differ in partitioning type (range or hash)
It’s very helpful feature if you want to quickly migrate to different type of index or use them during different period of time.
NOTE – to create such indexes you need to use INVISIBLE clause.
Tests data
create table test_tbl ( id1 number, id2 number, id3 number, id4 number );
Not using INVISIBLE clause steal leads to standard oracle error like in previous version before 12C:
ORA-01408: such column list already indexed
create unique index test_tbl_idx1 on test_tbl(id1);
create index test_tbl_idx2 on test_tbl(id1);
SQL Error: ORA-01408: such column list already indexed
drop index test_tbl_idx1;
Let’s make indexes on the same set of columns with different characteristic
Unique versus non-unique
create unique index test_tbl_idx1 on test_tbl(id1); create index test_tbl_idx2 on test_tbl(id1) invisible;
B*tree versus bitmap
create index test_tbl_idx3 on test_tbl(id2); create bitmap index test_tbl_idx4 on test_tbl(id2) invisible;
Different partitioning strategies
create index test_tbl_idx5 on test_tbl(id3, id4) global partition by range(id3, id4) (partition p1 values less than(10, maxvalue), partition p2 values less than(maxvalue, maxvalue)); create index test_tbl_idx6 on test_tbl(id3, id4) global partition by hash(id3, id4) partitions 4 invisible;
Switching to different version of index
It’s just standard feature of oracle 11G making indexes INVISIBLE and vice versa.
select index_name, uniqueness, partitioned, visibility from user_indexes where table_name='TEST_TBL' order by index_name; INDEX_NAME UNIQUENESS PARTITIONED VISIBILITY -------------- ---------- ----------- ---------- TEST_TBL_IDX1 UNIQUE NO VISIBLE TEST_TBL_IDX2 NONUNIQUE NO INVISIBLE TEST_TBL_IDX3 NONUNIQUE NO VISIBLE TEST_TBL_IDX4 NONUNIQUE NO INVISIBLE TEST_TBL_IDX5 NONUNIQUE YES VISIBLE TEST_TBL_IDX6 NONUNIQUE YES INVISIBLE alter index test_tbl_idx1 invisible; alter index test_tbl_idx3 invisible; alter index test_tbl_idx5 invisible; alter index test_tbl_idx2 visible; alter index test_tbl_idx3 visible; alter index test_tbl_idx6 visible; select index_name, uniqueness, partitioned, visibility from user_indexes where table_name='TEST_TBL' order by index_name; INDEX_NAME UNIQUENESS PARTITIONED VISIBILITY -------------- ---------- ----------- ---------- TEST_TBL_IDX1 UNIQUE NO INVISIBLE TEST_TBL_IDX2 NONUNIQUE NO VISIBLE TEST_TBL_IDX3 NONUNIQUE NO INVISIBLE TEST_TBL_IDX4 NONUNIQUE NO VISIBLE TEST_TBL_IDX5 NONUNIQUE YES INVISIBLE TEST_TBL_IDX6 NONUNIQUE YES VISIBLE
Have a fun 🙂
Tomasz