11g: Invisible Indexes

Hi Everyone!!

There is one good feature in 11g.!! Invisible indexes.

Invisible Indexes:

Invisible indexe is an index that is not visible to optimizer unless otherwise you specify the
OPTIMIZER_USE_INVISIBLE_INDEXES parameter to true in init.ora or system or session level.

Why Invisible Indexes:


* Its a new feature of 11g, where index has the ability to hide from Optimizer. This feature can be used where we use indexes infrequently to speed up the certain operation. Instead to drop we can make it invisible.

*It is different than unusable indexes as invisible indexes are maintained during DML operations. We do not have to rebuild when we want to use it. We can test the sql's performance before and after the index without dropping it actually.

How to create:

CREATE INDEX indexname ON tablename(column) INVISIBLE;

What is the Impact on EBIZ suite?

I see, No impact at this moment. Ebiz applicaiton products doesn't uses this feature as far as I know.

See you all in the next R12 article.

1 comment:

ben said...

Awesome Suresh. Thanks !
Badri