12c: Intro To Multiple Indexes On Same Column List (Repetition)

From an indexing perspective, one of the bigger ticket items introduced with Oracle Database 12c is the new capability to create multiple indexes on the same column list. It’s even a feature listed in the New Features Guide 🙂 This can be particularly useful when you want to change the index definition in some way but you want to reduce the time in which the corresponding columns might not indexed.

Previous to 12c, you could not create an index if the same column list is already indexed and would generate an ORA-01408: such column list already indexed error. So, if you wanted to change an index from being say a B-Tree index to a Bitmap index, or from being Unique to Non-Unique or from being Non-Partitioned to Partitioned in same manner, etc. then you had to first drop the index and re-create it again as required. This means for the period in which the index is being re-created (which could be a considerable period for a larger index), the column list is not covered by an index, which might prove to be problematic.

Here, we create a partitioned table and create a Non-Unique, Non-Partitioned index on the ID column:

SQL> create table ziggy (id number, name varchar2(30)) partition by range (id) (partition ziggy1 values less than (1000), partition ziggy2 values less than (2000), partition ziggy3 values less than (maxvalue));

Table created.

SQL> insert into ziggy select rownum, 'DAVID BOWIE' from dual connect by level <=5000;

5000 rows created.

SQL> commit;

Commit complete.

SQL> create index ziggy_id_i1 on ziggy(id);              

Index created.

SQL> alter table ziggy add constraint ziggy_pk primary key(id);

Table altered.

If we decide for whatever reason we want to have say a Unique index instead:

SQL> create unique index ziggy_id_i2 on ziggy(id);
create unique index ziggy_id_i2 on ziggy(id)
                                         *
ERROR at line 1:
ORA-01408: such column list already indexed

No good, we can’t, even if we make it initially INVISIBLE:

SQL> create unique index ziggy_id_i2 on ziggy(id) invisible;
create unique index ziggy_id_i2 on ziggy(id) invisible
                                         *
ERROR at line 1:
ORA-01408: such column list already indexed

Because the index is used to police a PK constraint, we can’t even just drop the index:

SQL> drop index ziggy_id_i1;
drop index ziggy_id_i1
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

We have to first drop or disable the PK constraint, then drop the index, then re-create the index.

SQL> alter table ziggy drop primary key;

Table altered.

SQL> drop index ziggy_id_i1;

Index dropped.

SQL> alter table ziggy add primary key(id) using index (create unique index ziggy_id_i2 on ziggy(id));

Table altered.

This means the constraint is not automatically enforced (unless we disable it with validate, thus locking the table) and the ID column is not available via an index during the entire duration of creating the new index.

The 12c database has given us more flexibility in this regard.

Providing we create the index as INVISIBLE (meaning there is only ever the one Visible index on the column list), we can now create multiple indexes on the same column list, providing the new index has a different characteristic to existing indexes. So with a 12c database:

SQL> create unique index ziggy_id_i2 on ziggy(id) invisible;

Index created.

We now have two indexes on the same column list (the ID column). An attempt however to create or alter an index such that two visible indexes have the same column list will fail:

SQL> alter index ziggy_id_i2 visible;
alter index ziggy_id_i2 visible
*
ERROR at line 1:
ORA-14147: There is an existing VISIBLE index defined on the same set of
columns.

This means we can now (say) replace the index policing the PK constraint quicker (or convert a B-Tree to a Bitmap index or convert a Non-Partitioned index to a Partitioned index, etc.) as we don’t now have to wait for the new index to be created first:

SQL> alter table ziggy drop primary key;

Table altered.

SQL> drop index ziggy_id_i1;

Index dropped.

SQL> alter index ziggy_id_i2 visible;

Index altered.

SQL> alter table ziggy add constraint ziggy_pk primary key(id);

Table altered.

We can create as many indexes as we like on the ID, providing only one index is ever visible at a time and providing all the indexes have some form of differing characteristic. For example, all these indexes could potentially all co-exist:

SQL> create index ziggy_id_i3 on ziggy(id) local invisible;

Index created.

SQL> create bitmap index ziggy_id_i4 on ziggy(id) local invisible;

Index created.

SQL> create index ziggy_id_i5 on ziggy(id) reverse invisible;

Index created.

Of course, having many multiple indexes on the same column list in this manner is very likely a very bad idea …