Wednesday, August 31, 2022

Find the number of times an SQL Index had been used

SYSTABLEINDEXSTAT times index 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.

1 comment:

  1. I used TABLE_NAME and TABLE_SCHEMA instead of INDEX_NAME and INDEX_SCHEMA in the WHERE-clause and it works vary fast. Also I can see INDEX in different schemas for a Table or physical file.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.