Dear Readers,
In this article, we will see the following Multiple Indexes On the Same Set Of Column in Oracle 12c.
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
|
1 2 3 4 |
SQL> show parameter optimizer_use_invisible_indexes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_use_invisible_indexes boolean FALSE |
Lets change the value
|
1 2 3 |
SQL> alter system set optimizer_use_invisible_indexes=TRUE; System altered. |
Again check parameter
|
1 2 3 4 |
SQL> show parameter optimizer_use_invisible_indexes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_use_invisible_indexes boolean TRUE |
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:-
|
1 2 3 |
SQL> create table acc_cust (sno number,sname varchar2(10),address varchar2(30)); Table created. |
Lets insert some records.
|
1 2 3 4 5 6 7 8 |
SQL> insert into acc_cust(sno,sname,address) values(101,'VINOD','19/45.....'); 1 row created. SQL> insert into acc_cust(sno,sname,address) values(102,'AJAY','14/85...'); 1 row created. SQL> insert into acc_cust(sno,sname,address) values(103,'SAI','16/58.....'); 1 row created. SQL> COMMIT; Commit complete. |
Check the records
|
1 2 3 4 5 6 7 8 |
SQL> SELECT * FROM acc_cust; SNO SNAME ADDRESS ---------- ---------- ------------------------------ 101 VINOD 19/45..... 102 AJAY 14/85... 103 SAI 16/58..... |
Create index on table(ACC_CUST)
|
1 2 |
SQL> create index test1 on acc_cust (SNO); Index created. |
Now, If you try to create another index on column SNO, you will get error message like below.
|
1 2 3 4 |
SQL> create index test2 on acc_cust (SNO); create index test2 on acc_cust (SNO) ERROR at line 1: ORA-01408: such column list already indexed |
Now, I am trying to create 12C new feature but I got error because index are physically not different.
|
1 2 3 4 |
SQL> create index test2 on acc_cust (SNO) invisible; create index test2 on acc_cust (SNO) invisible ERROR at line ORA-01408: such column list already indexed |
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.
|
1 2 |
SQL> create index test2 on acc_cust (SNO) reverse invisible; Index created. |
If I had not set this index to invisible, the index would not be created.
|
1 2 3 4 5 6 7 8 |
SQL> select index_name, index_type, visibility from user_indexes where table_name='ACC_CUST'; INDEX_NAME INDEX_TYPE VISIBILIT -------------------- --------------------------- --------- TEST2 NORMAL/REV INVISIBLE TEST1 NORMAL VISIBLE |
What is use of Multiple Indexes on the Same Set of Column?
-
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
|
1 2 |
SQL> explain plan for select SNAME, ADDRESS from acc_cust where SNO=101; Explained. |
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.
|
1 2 3 4 5 6 7 |
SQL> ALTER INDEX TEST1 INVISIBLE; Index altered. SQL> ALTER INDEX TEST2 VISIBLE; Index altered. |
Let’s check again visibility of index on column after changing
|
1 2 3 4 5 |
SQL> select index_name, index_type, visibility from user_indexes where table_name='ACC_CUST'; INDEX_NAME INDEX_TYPE VISIBILIT ------------------- --------------------------- --------- TEST2 NORMAL/REV VISIBLE TEST1 NORMAL INVISIBLE |
-
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.
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Loading...
