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

asked 15 Sep '15, 06:03

robert's gravatar image

robert
543253445
accept rate: 0%

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?

(15 Sep '15, 06:33) Volker Barth

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

(15 Sep '15, 12:01) 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.

(17 Sep '15, 07:08) Volker Barth

Thanks for the details, Volker

(18 Sep '15, 03:10) robert
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×26

question asked: 15 Sep '15, 06:03

question was seen: 743 times

last updated: 18 Sep '15, 03:10