Find the number of times an SQL Index had been used

When you are building SQL DDL Indexes to improve performance it is also imperative to find Indexes that are not being used. Having identified those a decision can be made on whether to delete these unused indexes, or not.

Finding this information introduced me to a SQL View I had not used before: SYSTABLEINDEXSTAT

SYSTABLEINDEXSTAT contains the columns I want, the number of times the index has been used and the date it was last used. Alas, the view SYSINDEXES does not contain that information.

I always recommend that, on the partition you use, you run the following statement at least once to see all of the information that is available to you:

SELECT * FROM QSYS2.SYSTABLEINDEXSTAT LIMIT 10 ;

In the partition I used to write these SQL examples I found that the SYSTABLEINDEXSTAT was verey slow, which is why I used the LIMIT to return only ten rows of results.

Playing in my library, MYLIB, I found it quicker, and a lot less frustrating, to build a table of the results just for my library.

01 CREATE TABLE QTEMP.OUTFILE AS 02 (SELECT * FROM QSYS2.SYSTABLEINDEXSTAT 03 WHERE INDEX_SCHEMA = 'MYLIB') 04 WITH DATA ;

By using this file the results would be returned to me in a couple of seconds, much faster. Below I am going to show statements using both SYSTABLEINDEXSTAT and my Output Table so you can use either way to get to the data you desire.

You can get the same information that I showed from SYSTABLEINDEXSTAT from the Output Table too:

SELECT * FROM QTEMP.OUTFILE ORDER BY INDEX_NAME ;

When looking at both the first thing I noticed was the results contained more rows than the number of indexes I have in my library.

In the below statement I am joining SYSTABLEINDEXSTAT to the Table function OBJECT_STATISTICS, using a lateral join.

01 SELECT A.INDEX_TYPE,A.INDEX_NAME,B.SQL_OBJECT_TYPE,B.OBJATTRIBUTE 02 FROM QSYS2.SYSTABLEINDEXSTAT A, 03 LATERAL 04 (SELECT SQL_OBJECT_TYPE,OBJATTRIBUTE 05 FROM TABLE(QSYS2.OBJECT_STATISTICS(INDEX_SCHEMA,'FILE',INDEX_NAME))) B 06 WHERE INDEX_SCHEMA = 'MYLIB' 07 ORDER BY 1,2 ;

Line 1: I am retrieving INDEX_TYPE and INDEX_NAME from SYSTABLEINDEXSTAT, and SQL_OBJECT_TYPE and the object's attribute, OBJATTRIBUTE, from OBJECT_STATISTICS.

Line 2: This line must end with a comma when I use a lateral join.

Line 4: I am retrieving only two columns from OBJECT_STATISTICS.

Line 5: I am using the columns, INDEX_SCHEMA and INDEX_NAME, from SYSTABLEINDEXSTAT as the input parameters for the OBJECT_STATISTICS Table function.

Line 7: Rather than give the column names I can just give the number of the columns in the result set to sort by.

If I was using the Output Table the equivalent statement would look like:

01 SELECT A.INDEX_TYPE,A.INDEX_NAME,B.SQL_OBJECT_TYPE,B.OBJATTRIBUTE 02 FROM QTEMP.OUTFILE A, 03 LATERAL 04 (SELECT SQL_OBJECT_TYPE,OBJATTRIBUTE 05 FROM TABLE(QSYS2.OBJECT_STATISTICS(INDEX_SCHEMA,'FILE',INDEX_NAME))) B 07 ORDER BY 1,2 ;

The difference between the two statement are:

Line 2: The Output Table is used.

Line 6 in the first SQL statement is deleted. As the Output Table only contains data from MYLIB I do not need to select for that in a Where clause.

The results are the same:

INDEX_TYPE INDEX_NAME OBJECT_TYPE OBJATTRIBUTE ---------- ------------ ----------- ------------ INDEX TABLE1INDEX INDEX LF INDEX ANY_INDEX INDEX LF LOGICAL PFILE1LF <NULL> LF PHYSICAL TESTFILE <NULL> PF PHYSICAL TESTFILE1 <NULL> PF PHYSICAL TESTFILE2 <NULL> PF

I only have two Indexes in my library. PFILE1LF really is a DDS logical file. The others are a mixture of SQL DDL Tables and Physical files. For the purposes of this post I only need to work with the Indexes.

SYSTABLEINDEXSTAT offers me two fields I want to use to determine how often an Index is used:

• QUERY_USE_COUNT:  The number of times the Index has been used
• LAST_QUERY_USE:  The timestamp of when the Index was last used

To get those results my SQL statement using SYSTABLEINDEXSTAT would look like:

01 SELECT INDEX_NAME, 02 DATE(CREATE_TIMESTAMP) AS "Created", 03 DATE(LAST_QUERY_USE) AS "Last used", 04 QUERY_USE_COUNT, 05 QUERY_STATISTICS_COUNT 06 FROM QSYS2.SYSTABLEINDEXSTAT 07 WHERE INDEX_SCHEMA = 'MYLIB' 08 AND INDEX_TYPE = 'INDEX' 09 ORDER BY 3,2,1 ;

And for using the Output Table it would look like:

01 SELECT INDEX_NAME, 02 DATE(CREATE_TIMESTAMP) AS "Created", 03 DATE(LAST_QUERY_USE) AS "Last used", 04 QUERY_USE_COUNT, 05 QUERY_STATISTICS_COUNT 06 FROM QTEMP.OUTFILE 07 WHERE INDEX_TYPE = 'INDEX' 08 ORDER BY 3,2,1 ;

Again I do not need to select the library in the Where clause as the Output Table only contains the data from my library.

As I am only interested in the date the Index was created and when it last used I have converted the timestamps in CREATE_TIMESTMAP and LAST_QUERY_USE to dates by using the Date scalar function.

The ORDER BY uses the column in the results number as I explained in an earlier example.

The results are the same:

INDEX_NAME Created Last used QUERY_USE_COUNT ------------- ---------- ------------- -------------- ANY_INDEX 2021-09-28 2021-09-28 2 TABLE1INDEX 2022-07-26 <NULL> 0

ANY_INDEX has been used twice, the last time on September 28, 2021.

TABLE1INDEX has never been used, therefore, the date is null and the number of times used is zero.

With this information I can determine if I want to delete an Index or keep it.

I could also get information about the Indexes' keys using this View:

01 SELECT INDEX_NAME,INDEX_TYPE, 02 NUMBER_KEY_COLUMNS AS "No. keys", 03 COLUMN_NAMES, 04 SYSTEM_TABLE_SCHEMA AS "Table lib", 05 SYSTEM_TABLE_NAME AS "Table" 06 FROM QSYS2.SYSTABLEINDEXSTAT 07 WHERE INDEX_SCHEMA = 'MYLIB' 08 AND INDEX_TYPE = 'INDEX' 09 ORDER BY INDEX_NAME ;

Due to the slowness of SYSTABLEINDEXSTAT I do not recommend you do so. Go get it from SYSKEYS View instead as this is much faster.

 

You can learn more about the SYSTABLEINDEXSTAT SQL View from the IBM website here.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.