[Db-derby Wiki] Update of "CheckingForIndexStatistics" by StanleyBradbury

[Apache Wiki <wi...@apache.org>]

Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by StanleyBradbury:
http://wiki.apache.org/db-derby/CheckingForIndexStatistics

New page:
## Added 2006-06-29

== 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 = 1 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)
}}}
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.