NewsCustom SearchContact
When ORA-01408: such column list already indexed really messes up
    by Ludek Bob Jankovsky, 18-Dec-2017 (ORACLE STUFFS)
You tried to create an index on a set of columns in a table, but you've already indexed this set of columns.

You could ask why do we have to index the same set of columns twice.
They assured us the version 12 would allow more indexes on the same set of columns, but it still does not work in the 12.1 version.
So back to the question - WHY?
Imagine you have got a global index and you want to replace it by a local one without putting down users dependent on the global index while it can take long to create indexes on a large enough table. You want to do that seamlessly and you have no downtime option.

I can imagine following reasons:

  • Service operation without downtime of the system - where you have to do some bulk DDL operations on particular partitions and that would set any global index into the UNUSABLE state. Users and processes need the index for proper working anyway.
  • Regular operation related to aging - Aging (ILM) sometimes requires DDL operations on partitions like MOVE or even DROP of particular partition. Any global index would be a show stopper. In my opinion, you should avoid global indexes as much as possible, but sometimes they help to increase query performance. So, we can use this temporary switch out and back in these cases.
  • Design changes - when we want to switch from the LOCAL to GLOBAL or vice versa seamlessly, so first create the new index and then remove the old one.

So we have a partitioned table "TABLE_A" with a global index "INDEX_A_1"(col1, col2).
We have to create similar index, but a local one: "INDEX_A_1L"(col1, col2) local.

create index INDEX_A_1L on TABLE_A (col1, col2) local

The attempt returns following error:

ORA-01408: such column list already indexed

There is a workaround. I never used much the option of ASCending or DESCending indexes, but here it helps.

create index INDEX_A_1L on TABLE_A (col1, col2 desc) local

Adding the desc option just rules out the consideration of the list of column as the same and that allows you to create "almost the same" index.

Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky