The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

v12.01.4142

Are there any system tables that would help in determining which indexes are being used and which are not?

asked 17 Feb '15, 14:07

Brian's gravatar image

Brian
76557
accept rate: 0%


Why do you expect such information in system tables? - As SQL Anywhere does usually optimize statements when they are executed (and not beforehand, i.e. not when views, stored procedures and the like are created/altered), it will depend on the actual queries whether an index will be used or not. You can have a look at the plans of a query to see what indexes are used.

Here are some possible helpful links from the v12.0.1 docs:


Or do you relate to the automatically created indexes for primary, unique and foreign keys? They will be used to check for unique key values and/or existing FK values (besides the normal usage of indixes to access rows efficiently).

permanent link

answered 17 Feb '15, 14:29

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

edited 17 Feb '15, 14:51

What we are trying to do is to eliminate un-needed indexes. Ex. On one table, we have 10 indexes and we would like to drop indexes that are not being used or rarely used.

(17 Feb '15, 14:55) Brian
Replies hidden

So we can assume these inxexes are "secondary" ones, i.e. not needed for PKs/UNIQUE KEYs/FKs? - Then you may use "typical queries" on this table to find out whether these indexes are used. The Index Consultant may be helpful here, too, as it can check whether secondary indexes are used or not.

(17 Feb '15, 15:10) Volker Barth
2

I can see how this would be useful; it's something that Gupta SQLBase used to have - a UseCount field in the sysindexes system table. We monitored it for indexes where the count wasn't increasing over a fairly long period and dropped them if they never got used.

However I imagine that there is considerable overhead to maintain such data, and it's noticeable that later versions of SQLBase don't support it.

If you have a lot of dynamic SQL, effective checking of plans for which indexes are selected for use is going to be tricky, and can change as the distribution of data changes. At the end of the day I think you are going to have to rely on your knowledge of your product and therefore they sort of queries that are going to be run, to decide what is likely to be useful.

There are two things in your favour though, SQL Anywhere automatically optimizes duplicate indexes and only maintains one physical version. Second, unless you have very high transaction throughput, the actual overhead of maintaining indexes seems to be pretty low, compared with everything else going on.

(17 Feb '15, 15:37) Justin Willey
Your answer
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: 17 Feb '15, 14:07

question was seen: 495 times

last updated: 17 Feb '15, 15:37