12c - Multiple Indexes on the same set of Columns

Prior to Oracle Database 12c, when trying to create an index for a column or column list already indexed, occurred the error ORA-01408: such column list already indexed error .

One of the new features of 12c is the ability to have multiple indexes on the same column or columns list.

Creating an index on a column or list of columns that have already been indexed is simple and you have to ensure that only one index will be visible.

If the index already created is visible , then you will have to use the keyword invisible to the creation of new content.

Some reasons for using this new feature:

SQL> create table Table_test
(
column1 number,
column2 number,
Column3 number,
coluna4 number
); 

Table created.

- Unique versus nonunique

SQL> create unique index on Table_test_idx1 Table_test (column1);

Index created.

SQL> create unique index on Table_test_idx2 Table_test (column1);

Index created.

 - B-tree versus bitmap

SQL> create index Table_test_idx3 on Table_test (Column2);
Index created.

SQL> create bitmap index Table_test_idx4 on Table_test (Column2) invisible; Index created.

SQL> create index Table_test_idx5 on Table_test (Column3, coluna4)
global partition by range (Column3, coluna4)
(partition part1 values less than (100, 200),
partition PART2 values less than (maxvalue, maxvalue));

Index created.

SQL> create index Table_test_idx6 on Table_test (Column3, coluna4)
global partition by hash (Column3, coluna4)
partitions 10 invisible;

Index created.

Modifying which index is visible:

SQL> ALTER INDEX Table_test_idx1 INVISIBLE;

Index altered.

SQL> ALTER INDEX Table_test_idx2 VISIBLE;

Index altered.

Making the optimizer considers the invisible indices:

SQL> alter session set optimizer_use_invisible_indexes = true;

Session altered.

SQL> select count (*) from Table_test;

Execution Plan
------------------------------------------------ ----------
Plan hash value: 3185905804

----------------------------------- ---------------------------------------------
| Id | Operation | Name | rows | Cost (% CPU) |
-------------------------------------------------- ------------------------------
| 0 | SELECT STATEMENT | | 1 | 0 (0) |
| 1 | SORT AGGREGATE | | 1 | |
| 2 | BITMAP CONVERSION COUNT | | 1 | |
| 3 | BITMAP INDEX FAST FULL SCAN | TABLE_TEST_IDX4 | | |
-------------------------------------------------- ------------------------------



SQL> alter session set optimizer_use_invisible_indexes = false;
Session altered.

SQL> select count (*) from Table_test;

Execution Plan
------------------------------------------------ ----------
Plan hash value: 1737391099

----------------------------------- ----------------------------------------
| Id | Operation | Name | rows | Cost (% CPU) | Time |
-------------------------------------------------- -------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0) | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL | TABLE_TEST | 1 | 2 (0) | 00:00:01 |
-------------------------------------------------- -------------------------

Understand When to Create Multiple Indexes on the Same Set of Columns

You can create multiple indexes on the same set of columns when the indexes are different in some way. For example, you can create a B-tree index and a bitmap index on the same set of columns.

For example, the following steps create a B-tree index and a bitmap index on the same set of columns in the oe.orders table:

    Create a B-tree index on the customer_id and sales_rep_id columns in the oe.orders table:

    CREATE INDEX oe.ord_customer_ix1    ON oe.orders (customer_id, sales_rep_id);

    The oe.ord_customer_ix1 index is visible by default.

   Alter the index created in Step 1 to make it invisible:

    ALTER INDEX oe.ord_customer_ix1 INVISIBLE;

    Alternatively, you can add the INVISIBLE clause in Step 1 to avoid this step.

    Create a bitmap index on the customer_id and sales_rep_id columns in the oe.orders table:

    CREATE BITMAP INDEX oe.ord_customer_ix2    ON oe.orders (customer_id, sales_rep_id);

    The oe.ord_customer_ix2 index is visible by default.

    If the oe.ord_customer_ix1 index created in Step 1 is visible, then the CREATE BITMAP INDEX statement in this step returns an error.

·       When you have multiple indexes on the same set of columns, only one of these indexes can be visible at a time, and any other indexes must be invisible.