db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Db-derby Wiki] Update of "CheckingForIndexStatistics" by StanleyBradbury
Date Thu, 29 Jun 2006 21:50:23 GMT
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:

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)
        |2005-10-25 12:36:53.62      
        |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.

View raw message