• Database Blog
  • GoldenGate 12c
    • Top 50 Oracle Goldengate Interview Questions To Get the Job!
  • Master Oracle 12c DBA
  • Oracle Tutorials
    • Oracle Tips & Tricks
    • Oracle VirtualBox
  • Learn Excel

VitalSoftTech

Database Administration | Oracle | Oracle Training | DBA Support | Oracle 12c DBA | GoldenGate | GoldenGate 12c | DBA Interview Questions | Goldengate Interview Questions | Natik Ameen

  • Digital Marketing
  • Blogging Tutorial and Tips
  • Cool Names
  •   Login
« Previous Post
Next Post »

Oracle 12c Database Multiple Indexes On Same Set of Columns

July 2, 2015 by Natik Ameen   /  Oracle 12c

Oracle database 12c has about 500+ new features when compared to 11gR2. Among these features there will be some which get the mainstream attention. But often little caveats or enhancements prove to be as vital as the big ones. One such enhancement is the ability to have multiple indexes on same column or same set of columns. Lets look at this feature and the possible uses cases.

Creating Oracle 12c Database Multiple Indexes On Same Column

The creation of an index over a column or set of columns which have already been indexed is simple. You just have to ensure one thing and i.e. only one index is visible at any time. If already created index is visible then you will have to use the invisible keyword to make sure that newly created index is invisible. For example we have a table T1 in which C1 column is Primary Key and hence this column is already indexed.


SQL> select index_name from user_indexes where table_name='T1';
INDEX_NAME
----------------------------------------
SYS_C009869


Now if you try to create another index on Column C1 then you will get the error message below.


SQL> create index idx_t1 on t1(c1);
create index idx_t1 on t1(c1)
*
ERROR at line 1:
ORA-01408: such column list already indexed


However the following command will work and will create an additional index on the same C1 column. This was not possible until 11gR2.


SQL> create index idx_t1 on t1(c1) invisible;
Index created.


If you want you can go ahead and create another index, this time let it be a bitmap index.


SQL> create bitmap index idx_c1_bm on t1(c1) invisible;
Index created.

SQL> select index_name from user_indexes where table_name='T1';
INDEX_NAME
----------------------------------------
IDX_T1
IDX_C1_BM
SYS_C009869

Use Cases

Although you can now create more than one indexes on the same set of attributes but why would you want to do that. I mean creating an index takes time, space and also has operational overhead. Here are a couple of very good use cases in which you can use this new feature of a 12c database.

Testing Index Performance

The first use case can be to test the performance of newly created index. Suppose you already have a b-tree index on some columns and you want to test whether creating bitmap index on same columns can improve performance or not. You can create another bitmap index on same column just like we did above.


Once created you can now set the following session parameter to make invisible indexes visible to Optimizer.


SQL> alter session set optimizer_use_invisible_indexes=true;
Session altered.

Once this is done you can now test the performance of your new index although it will still be invisible. Once tested, you can now decide whether to keep b-tree index or the bitmap index. You can go ahead and drop either one of them.

Index Availability

The second not so obvious use case is to ensure that indexes remain available during operations like converting bitmap index to b-tree, local index to global in partitioned table, converting normal index to reverse key index and also replacing the index being used by some Primary Key constraint. All of these operations required indexes to remain unavailable to some specific period of time.


For example if you want to change a certain index but that index is being used by some Primary constraint then you will first have to disable the constraint, drop the index, rebuild it and finally enable the constraint again. Now the rebuilding process can take a lot of time especially if your data size is huge. And during all that time your table will be out of any Primary Key and an index on ID column. This can be very serious and can cause the denial of service attack on your database.


However with this multiple index feature you can now get rid of this downtime caused by rebuilding of index. You can create an index beforehand and mark it invisible. Like in our example above the IDX_T1 is on same identity column of T1 and is invisible. However the Primary Key is using the other index i.e. SYS_C009869.


Now if you want to change this then you can execute the following commands. All commands will take hardly second or two.


SQL> alter table t1 drop primary key;
Table altered.

SQL> drop index SYS_C009869;
Index dropped.

SQL> alter table t1 add constraint t1_pk primary key (c1);
Table altered.


Thus you have  successfully changed the Primary Key index and table was without any index for hardly a couple of seconds.


Oracle 12c via Multiple Column Indexes feature thus allows you to have a lot of flexibility in managing indexes and saves a lot of downtime.


Read more on Oracle 12c Database Default Column Value Enhancement.

Filed Under: Oracle 12c Tagged With: 12c Database, 1Z0-060, CDB, Oracle 12c, PDB

Comments

  1. tamizhchirp@gmail.com'tamil says

    May 10, 2018 at 8:21 pm

    Thank you for your effort in writing this interesting article.

    Reply

Leave a Reply Cancel reply

Your email address will not be published.

« Previous Post
Next Post »

Join over 3,000 others My posts. Your Inbox. Beautiful.

Database    GoldenGate

Natik Ameen

About Natik Ameen

Natik Ameen is an Oracle Production DBA, Oracle Certified RAC Expert and a DBA track Certification trainer for over 17 years. He is an Oracle Evangelist and has presented at IOUG & UTOUG conferences. He writes on topics such as Database Administration, RAC, GoldenGate and the Cloud.

Who is Natik Ameen?

Guides

  • My GoldenGate Tutorials
  • Oracle 12c Database Tutorials
  • GoldenGate GGSCI Command Series
  • My OraTips and Tricks

Popular Blogs

  • My GoldenGate Home Page
  • Top 50 Oracle Goldengate Interview Questions To Get the Job!
  • Top 30 RAC Interview Questions That Helped Me. Are You Prepared?
  • Oracle GoldenGate Concepts and Architecture Made Simple!
  • Virtualize Oracle RAC 12c on My Laptop
  • Step by Step Oracle 12c Database Install on Virtual Box

Popular Oracle Articles

Oracle Announces Beta Availability of Oracle Database 12c Release 2
Top 5 Announcements at Oracle Open World
News on Oracle Database STANDARD EDITION 12.1.0.2

Technology Centers

Oracle Business Intelligence and DW
Cloud Computing, SOA, Virtualization
Big data, Java, Linux, PLSQL, Security
Enterprise Architecture, Digital Experience

Oracle Documentation

10g R1, 10g R2, 11g R1, 11g R2, 12c R1
Oracle CPU's, EM 11g, EM 12c
GoldenGate 12c, Oracle VM, VirtualBox

Copyright ©2021

BLOGOracle Tutorials