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 |
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:
Thanks for the details, Volker