When you create an index, it is immediately created, it does not need to become "active" - the statement will only return when the index has been built (or will fail with a blocking timeout if the according table cannot be locked as required because of other transactions). You can only create "logical" indixes, the database engine will decide whether it can "re-use" an already existing physical index or has to create one (and the latter will take more time...).

That being said, I'm not sure whether cached plans will be thrown away when a new (physical) index is added.

Have you tried to use the Index Consultant - or tried with "VIRTUAL" indexes and the PLAN() function?

Hi Volker Thanks for your explanations – very helpful. I have tried with Index Consultant. I did get hints about table column layout not optimal (Overall database performance) but not in the more interesting performance of the database application. I did not try VIRTUAL indexes and the PLAN() function. May be I should try them. Regards, Robert

Just for the record: The v16 docs do contain more information on the locking during the index creation, cf. that "What's New" section - note that the behaviour of "previous releases" applies to v12.0.1, too:

Concurrent index building In previous releases, the CREATE INDEX statement acquired an EXCLUSIVE table lock when an index was being built. Now, the operation acquires an EXCLUSIVE table lock for short periods of time at the beginning and at the end of the operation and a SHARED lock for most of the operation, so that other connections can access the table data while the index is being created. The connection creating the index is blocked from accessing the table until the index is created. You must upgrade existing databases to use this feature. See CREATE INDEX statement.

Thanks for the details, Volker