Friday, July 05, 2013

12c - Multiple same column indexes...

Another new capability of Oracle Database 12c is the ability to create more than one index on the same set of attributes.  That is - you can have two or more indexes on the column "X", or the set of columns "A,B,C".

So, that would beg the question "why - why would you want to do that?".  The answer is twofold - testing and availability.  The testing part is obvious - you might want to test how the performance of a bitmap index would be in your reporting system as compared to a b*tree index on the same set of columns.  You can now create that bitmap index even though the b*tree exists, test it out in your session, and then decide to get rid of the b*tree index in favor of the bitmap (or get rid of the bitmap of course).

The availability part isn't as obvious perhaps.  If you wanted to roll out a change to production that would replace a normal b*tree index with a reverse key index - that would have called for an outage in the past.  You would have to drop the original index and then create the new one.  During the period of time the index was initially dropped and the new one completed work, it is likely the applications that depended on that index for data retrieval would have to be offlined.  If they were not - their performance, in light of the missing index, could be disastrous - in effect, a denial of service attack on the database.

In Oracle Database 11g and before - this is what we would expect if we attempted to create more than one index on the same set of attributes:

ops$tkyte%ORA11GR2> create table t ( x int, y int, z int );
Table created.

ops$tkyte%ORA11GR2> create index t_idx on t(x,y);
Index created.

ops$tkyte%ORA11GR2> create bitmap index t_idx2 on t(x,y);
create bitmap index t_idx2 on t(x,y)
                                *
ERROR at line 1:
ORA-01408: such column list already indexed

ops$tkyte%ORA11GR2> create bitmap index t_idx2 on t(x,y) invisible;
create bitmap index t_idx2 on t(x,y) invisible
                                *
ERROR at line 1:
ORA-01408: such column list already indexed


as you can see - it just wasn't going to happen.  But now in 12c - as long as only one index is "visible" - we can create multiple indexes:

ops$tkyte%ORA12CR1> create table t ( x int, y int, z int );
Table created.

ops$tkyte%ORA12CR1> create index t_idx on t(x,y);
Index created.

ops$tkyte%ORA12CR1> create bitmap index t_idx2 on t(x,y) invisible;
Index created.

Now that the second index is there, we can test it - to evaluate the performance for example - easily:

ops$tkyte%ORA12CR1> alter session set optimizer_use_invisible_indexes=true;

Session altered.

ops$tkyte%ORA12CR1> exec dbms_stats.set_table_stats( user, 'T', numrows => 1000000, numblks => 100000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA12CR1> set autotrace traceonly explain
ops$tkyte%ORA12CR1> select count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1106681275

---------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Cost (%CPU)|
---------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     1 |     0   (0)|
|   1 |  SORT AGGREGATE               |        |     1 |            |
|   2 |   BITMAP CONVERSION COUNT     |        |  1000K|            |
|   3 |    BITMAP INDEX FAST FULL SCAN| T_IDX2 |       |            |
---------------------------------------------------------------------

ops$tkyte%ORA12CR1> set autotrace off

if we deemed that index to be what we really wanted - we would just drop the old index and make this one visible, or set the old one invisible and set this one visible - publish it in effect.

See http://docs.oracle.com/cd/E16655_01/server.121/e17906/chapter1.htm#FEATURENO09740 and especially http://richardfoote.wordpress.com/2013/07/02/12c-intro-to-multiple-indexes-on-same-column-list-repetition/ for more info.  (and subscribe to Richard's blog if you aren't already - best index information out there)


POST A COMMENT

2 Comments:

Anonymous Franck Pachot said....

Hi Tom,

>> If you wanted to roll out a change to production that would replace a normal b*tree index with a reverse key index - that would have called for an outage in the past.

That's right for bitmap, but for reverse we can do this online:
alter index ... rebuild reverse online;

Regards,
Franck.

Sat Jul 06, 01:04:00 PM EDT  

Blogger Thomas Kyte said....

@Franck

true enough - in enterprise edition...

but this would also apply to non-partitioned to partitioned, unique to non-unique, etc...

Sun Jul 07, 12:01:00 AM EDT  

POST A COMMENT

<< Home