Multiple indexes on the same column

Multiple indexes on the same column
Until the release Oracle 11g was not possible to create more indexes on the same
column of a table.
Starting from release Oracle 12c, you can create multiple indexes on the same column
as long as the index type is different. However, only one type of index is usable /
visible at a given time.
To test the indexes invisible, you must set the init.ora parameter:
optimizer_use_use_invisible_indexes = true;
Not using INVISIBLE clause will result in the error:
ORA-01408: such column list already indexed
An example:
create unique index test_idx1 on test_tab (id1);
index created.
create index test_idx2 on test_tab(id1);
SQL Error: ORA-01408: such column list already indexed
Instead using invisible clause …
create unique index test_ idx1 on test_tab(id1);
index created.
create index test_idx2 on test_tab(id1) invisible;
index created.
To see the data in the dictionary …
select index_name, uniqueness, partitioned, visibility
from user_indexes where table_name=’TEST_TAB’
order by index_name;
INDEX_NAME     UNIQUENESS PARTITIONED VISIBILITY
————– ———- ———– ———-
TEST_IDX1 UNIQUE     NO          VISIBLE    
TEST_IDX2  NONUNIQUE  NO          INVISIBLE

You may also like...

Leave a Reply

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