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.
Details to follow when 12c becomes available.
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 UTC Oct 5,2012 |
[...] Jonathan Lewis – Indexing 12c and Partitioning 12c [...]
Pingback by Oracle Database 12c and its features… « musingdba — October 18, 2012 @ 7:21 pm UTC Oct 18,2012 |
[...] 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 UTC Mar 4,2013 |