Multiple Indexes On The Same Set Of Column -12cR1

Multiple Indexes On The Same Set Of Column

You can create multiple indexes on the same set of columns but only one of the indexes can be visible at a time and the indexes are physically different. Physical different means it is not possible to create two B-tree index on the same set of column. If you are going to create a visible index then all existing indexes on the set of columns must be invisible. Alternatively, if you have a visible indexes on the set of column then you can go ahead and create another invisible index on the same set of column.
Before going to create multiple indexes on the same set of columns, one of the things that you need to be aware of is that we need to set dynamic parameter optimizer_use_invisible_indexes TRUE. By default, this is set to FALSE.

Check the parameter

Lets change the value

Again check parameter

If you have noticed, it is dynamic parameter and it is set TRUE now.

Creating Multiple Index:-

If already created index is visible to you then you need to make sure that which one index you are going to create on same set of column should be use the invisible keyword.
Let’s create one table and insert some rows:-

Lets insert some records.

Check the records

Create index on table(ACC_CUST)

Now, If you try to create another index on column SNO, you will get error message like below.

Now, I am trying to create 12C new feature but I got error because index are physically not different.

To create another index, index property should be different so let’s create Reverse index and we need to use invisible also. This was not possible until 11G.

If I had not set this index to invisible, the index would not be created.

What is use of Multiple Indexes on the Same Set of Column?

  1. To Taste Index Performance
Here, As an example if I have a b-tree index on the column but you want to check bitmap index performance on the same column. After testing that your decision that you want to keep which one index on the same column.

Please look into below example

SQL> set linesize 132

Optimizer used reverse index because it is visible to optimizer as we already set OPTIMIZER_USE_INVISIBLE_INDEXES parameter to TRUE.
Here, You can change visibility of index also.

Let’s check again visibility of index on column after changing

  1. Availability of index

In case your data size is huge then rebuild index will take a lot of time and on that whole time your table will be out of any index on column and also out of any primary key. With the help of this new feature you can create another index on same table and mark invisible existing index. This is also useful if you want to quickly migrate to a different index type.
If you will set dynamic parameter optimizer_use_invisible_indexes to false then it uses the only visible index available.
Needless to say, if table has more indexes then slower the execution while updating and inserting a table.



Thanks ……..


Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Add Comment