Can somebody tell me if Oracle maintains 2 indexes if I create a function-based index (one normal and another one with the translation) ? Also, if I have function based index on a table, does it affect the performance of insert, update and delete on the table?
I can't find any document regarding the performance or oracle maintaining 2 indexes internally. But one of the DBAs suggest that it is the case and asking us to create an additional column with the upper(column) and use a normal index.
If you create function-based index, oracle will act exactly the same as with any "normal" index - there will be no two sepparated indexes to support a single function based index, there is absolutely no need for oracle to do this internaly. And the internal structure of that function-based index will be the same as with any other B*Tree index.
And yes, of course function-based index will have an effect on inserts, updates or deletes, just like any other B*Tree index does (well, function-based index will have a slightly higher owerhead actually).
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?