CAN WE CREATE MULTIPLE INDEXES ON THE SAME COLUMN?

CAN WE CREATE MULTIPLE INDEXES ON THE SAME COLUMN?

It is not possible to create multiple straight forward or visible index in the same column of the table.

SQL> BEGIN
  for x in 1 .. 10 loop
  execute immediate 'CREATE index ind_'||x||' on bline(name)';
  END loop;
END;
/  2    3    4    5    6
BEGIN
*
ERROR at line 1:
ORA-01408: such column list already indexed
ORA-06512: at line 3

But invisible indexes can be created with different structure.

2 b-tree cannot be created on the same column.

One bitmap invisible and b-tree can be created on same column.

SQL> CREATE index ind_2 on bline(name) invisible;
CREATE index ind_2 on bline(name) invisible
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> CREATE bitmap index ind_2 on bline(name) invisible;

Index created.

Leave a Reply

%d bloggers like this: