Effectiveness of an applied index

[huber1]

Hi All

I am trying to optimize our database by applying indices. When I apply an index in Interactive SQL, I assume (and it seems) it get's immediately active, i. e. it is immediately in effect? To see the effect/result of the applied index, I run our application. It would be very helpful if I would get some confirmation on my assumption that an index is immediately valid.

– SQL Anywhere 12.0.1, OS X 10.10.5, Java 8

Best regards, Robert

[VolkerBarth]

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?

[huber1]

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

[VolkerBarth]

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.

[huber1]

Thanks for the details, Volker