Oracle Scratchpad

October 4, 2012

Indexing 12c

Filed under: 12c,Indexing,Oracle — Jonathan Lewis @ 2:43 pm GMT Oct 4,2012

Another little detail that Hermann Baer mentioned in his presentation yesterday was the ability to create multiple indexes with the same column definition – something which currently gets you Oracle error “ORA-01408: such column list already indexed.” 

No details, and there’s always the “safe harbour” slide of course – the one which says seomthing about the presentation being only an indication of current thinking and nothing is guaranteed to appear.

Having said that, this looks like an interesting option for those (possibly rare) occasions when you want to change a unique index into a non-unique index (for example, to change a unique constraint to deferrable). Rather than having to drop the index and create a new one – leaving the table unindexed while the index builds, you appear to have the option to: “create new index online”, “drop old index”. Moving a primary key constraint from one index to the other might not be so easy, of course, especially if there are foreign keys in place – but this certainly looks like a helpful step. [Update: actually it’s easy to move the constraint – as I subsequently found in this post.]

Details to follow when 12c becomes available.

Update Sept 2013

Although you can create multiple indexes with the same column definition, only one of them can be visible at any time – so this should remove the temptation that Richard describes in his comment below. It won’t stop people creating “duplicates”, though, and leaving some of them invisible for a while just in case they need to change their minds.  Always keep firm control of your indexing.

 

4 Comments »

  1. I think this will certainly be a handy new option when you want to re-create an index with new attributes. My initial concern when I first heard about this feature though was that this new flexibilty could be a very dangerous thing in the wrong hands. I can easily imagine databases all over place becoming over-indexed with numerious indexes on the same column list simply because you can with little thought. Should the index be a b-tree or a bitmap, unique or non-unique, partitioned or not, I know what, I’ll create 6 and play it safe :) If nothing else, the ORA-01408 offered some kinda protection from such “abuse”.

    Comment by Richard Foote — October 5, 2012 @ 12:04 am GMT Oct 5,2012 | Reply

  2. […] Jonathan Lewis – Indexing 12c and Partitioning 12c […]

    Pingback by Oracle Database 12c and its features… « musingdba — October 18, 2012 @ 7:21 pm GMT Oct 18,2012 | Reply

  3. […] don’t think this is likely to happen on a production system (until 12c) – but look what you can do if you try hard […]

    Pingback by Duplicate indexes ? | Oracle Scratchpad — March 4, 2013 @ 7:51 pm GMT Mar 4,2013 | Reply

  4. […] in the lounge waiting to be called for my flight I was musing on the 12c feature of having multiple indexes defined on the same ordered column set  when a thought crossed my mind and I decided to run a […]

    Pingback by Modify PK | Oracle Scratchpad — February 1, 2014 @ 11:00 am GMT Feb 1,2014 | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,524 other followers