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 Mon, 22 Mar 2010 19:34:49 GMT
Dear Wiki user,

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

The "CheckingForIndexStatistics" page has been changed by StanleyBradbury.
http://wiki.apache.org/db-derby/CheckingForIndexStatistics?action=diff&rev1=2&rev2=3

--------------------------------------------------

  ## Added 2006-06-29
- 
+ ## Updated 2010-03-22
  == 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. 
+ 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. {{{
+ 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)", 
+  select tablename || ' (' || CONGLOMERATENAME || ')' as "Table (Index)",
-       CASE WHEN CAST(creationtimestamp AS varchar(24))  IS NULL 
+       CASE WHEN CAST(creationtimestamp AS varchar(24))  IS NULL
-       THEN 'Recreate Index to Initialize' 
+       THEN 'Recreate Index to Initialize'
-       ELSE CAST(creationtimestamp AS varchar(24))  END 
+       ELSE CAST(creationtimestamp AS varchar(24))  END
- from sys.systables t, sys.sysconglomerates c 
+ 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                           
+         |2
  ----------------------------------------------
  -------------------------------------
  TABLE1 (TABLE1IDX)
-         |2005-10-25 12:36:53.62      
+         |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 - 10.2 '''builds since svn rev. 464683 - '''10.1 '''builds since svn rev.
632065
+ 
+ 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.
  

Mime
View raw message