Multiple Indexes on Same Set of Columns Oracle Database 12C release 1 (12.1)

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

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *