Multiple Indexes On The Same Column Or Set of Columns | Tech News

Chemicloud Web Hosting

Web Hosting

Starting with Oracle 12c, multiple indexes on same column or same set of columns can be created, as long as only one index is visible, and the indexes are physically different. It is not possible to create two bitmap indexes on the same column or set of columns.

Example:

Create a table ind_test1
SQL> create table ind_test1(id number, dept  varchar(20), start_dt date);
Table created.
Insert rows
SQL>  insert into ind_test1 values(111, 'TEST', to_date('01/01/2000','mm/dd/yyyy'));
1 row created.
SQL>  insert into ind_test1 values(222, 'DEVL',  to_date('10/25/2011','mm/dd/yyyy'));
1 row created.
SQL>  insert into ind_test1 values(333, 'TEST',to_date('05/22/1999','mm/dd/yyyy'));
1 row created.
Select * from ind_test1 table
SQL> select * from ind_test1;
ID DEPT                 START_DT
---------- -------------------- ---------
111 TEST                 01-JAN-00
222 DEVL                 25-OCT-11
333 TEST                 22-MAY-99

 

 

Create the first index on start_dt column. By default, Oracle creates B-tree index.

SQL>  create index idx1_ind_test1 on ind_test1(start_dt);
Index created.


 

Now, create a second invisible index on the same column

SQL> create index idx2_ind_test1 on ind_test1(start_dt) invisible;
create index idx2_ind_test1 on ind_test1(start_dt) invisible
*
ERROR at line 1:
ORA-01408: such column list already indexed

 

Index failed because these 2 are the same type of indexes.

You can create a bitmap index on the same column.

SQL>  create bitmap index  idx2_ind_test1 on ind_test1(start_dt) invisible;
Index created.

 

This feature is useful when you want to test the performance of different indexes without dropping the existing one. If you already have a b-tree index on a set of columns and you want to test whether creating bitmap index on the same set of columns will improve performance, you can make the existing one invisible and create a new visible one and perform the test.

 

This is also useful if you want to quickly migrate to a different index type.

 

 

You might also like More from author

Comments are closed.