jump to navigation

12c: Intro To Multiple Indexes On Same Column List (Repetition) July 2, 2013

Posted by Richard Foote in 12c, Multiple Indexes, Oracle Indexes.
trackback

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 …

About these ads

Comments»

1. Craig - July 3, 2013

Nice new feature. Just curious, did you look to see if the 2nd index will utilize the 1st index when it is creating itself? Having the new index build faster would be another nice benefit of this change.

Richard Foote - July 3, 2013

Hi Craig

That’s a good question. The answer is that it depends and works very much in the same way as it does when an index rebuild operation is performed. If the index is created “offline” as in my demo above, then indeed an existing index can be used. If however I use an “online” index build, then the base table is accessed.

I’ve discussed this previously for index rebuilds:

http://richardfoote.wordpress.com/2012/05/15/index-rebuild-does-it-use-the-index-or-the-table-nothing-touches-me/

2. Alex Antonov - July 15, 2013

Hi Richard
Interesting, when I altered session to use invisible indexes, it looks like CBO is capable to assess each of them and pick one with the lowest cost. I created (using your scripts) *i2, *i3, and *i4 invisible indexes only and ran a select * from ziggy whete id = 50; Oracle choose i2. Then I recreated the indexes having changed the names so i2 became i4 and i4 became i2 respectively. When I ran the same query Oracle choose i3. It also said it would use table access by index rowid batched. Do you know what that batched actually means?

Alex

Richard Foote - July 19, 2013

Hi Alex

This is actually one of the nice benefits of allowing multiple indexes in this manner in that you can setup a session to use invisible indexes and test to see if a new potential index might be beneficial.

The index rowid batched basically means Oracle can potentially access a number of rowids and sort them out into distinct blocks and so only access each distinct table block the once.

3. Oracle Database 12c New Features | Maria's Oracle Blog - August 8, 2013

[…] are a bunch of articles already about this new feature, have a look at Tom Kyte’s Blog and Richard Foote’s Blog […]

4. DB Oriented - January 5, 2014

[…] Multiple indexes on the same column list: it is possible to define several indexes on the same column list, as long as they are different somehow (unique vs. non-unique, b*tree vs. bitmap, etc.) and only one of them is visible [a post by Richard Foote] […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,894 other followers

%d bloggers like this: