Got Cardinality Statistics for your indexes? (and how to get them)

If you are not familiar with cardinality statistics you will find it useful to read the section "What are cardinality statistics?" in the Derby Tuning Guide. This Tip deals with initializing statistics for indexes that may have been created when there was no data in the table.

The Derby optimizer selects optimal query plans for most dataset queries without using index cardinality statistics but there are times where having this information can yield better performance. Derby initializes index statistics when the index is created but can only do so if there is some data already in the table. Creating indexes on empty tables will NOT initialize the statistics. Once initialized, however, Derby automatically maintains the statistics by updating the information each time a table scan is performed.

So here is a good rule of thumb: Always create indexes after at least a few records have been inserted. Note that this means creating all primary and foreign keys using "ALTER TABLE" so data can be loaded before the keys and the supporting indexes are created.

The following query can be used to check if there are indexes in the database for which statistics were not initialized. This query lists all indexes and the date and time the statistics were created. If statistics have not been initialized it prints 'Recreate Index to Initialize' instead of the creation timestamp.

 select tablename || ' (' || CONGLOMERATENAME || ')' as "Table (Index)",
      CASE WHEN CAST(creationtimestamp AS varchar(24))  IS NULL
      THEN 'Recreate Index to Initialize'
      ELSE CAST(creationtimestamp AS varchar(24))  END
from sys.systables t, sys.sysconglomerates c
    LEFT OUTER JOIN sys.sysstatistics s ON c.conglomerateid = s.referenceid
where t.tableid = c.tableid
and c.isindex = true and t.tabletype = 'T'  ;
Table (Index)
        |2
----------------------------------------------
-------------------------------------
TABLE1 (TABLE1IDX)
        |2005-10-25 12:36:53.62
TABLE2 (TABLE2IDX)
        |Recreate Index to Initialize
T2 (SQL060524012835340)
        |Recreate Index to Initialize
T3 (SQL060524012836170)

Initializing Statistics

Version 10.3 and higher plus version 10.2 builds since svn rev. 464683 AND 10.1 builds since svn rev. 632065

Use the Compress Table Utility. See Derby feature 737 for more information:

Derby-737: enhanced the Compress Table utility so that it will initialize index statistices for indexes that it rebuilds. Use Compress table to establish cardinality statistics for tables that contain data when running the above listed versions.

Previous versions:

For indexes without statistics, assuming that there is now data in the tables, dropping and recreating the indexes will establish the statistics. Use the DBLOOK tool to assist with recreating the indexes and keys. DBLOOK separates indexes that support a key from non-key indexes so you can determine the proper DROP syntax to use and also provides the CREATE INDEX or ALTER TABLE statement needed to re-establish the index.

CheckingForIndexStatistics (last edited 2012-03-30 17:52:35 by BrettBergquist)