INDEXSTATISTICS System Table Description

Description

A pseudo table generated to display statistics used by the NuoDB query optimizer.

Caution: Client applications should never contain code that accesses SYSTEM pseudo tables as they are subject to, and furthermore likely to, change from release to release.

Fields

Field Type Description
OBJECTID integer The ID for the database object
CATALOGID integer The catalog ID for the database object
SCHEMA string The schema in which the index was created
TABLENAME string The table name on which the index exists
INDEXNAME string The name of the index
KEYCOUNT bigint The number of keys in the index - should be the same as the cardinality of the table
VALID boolean True if the statistics were collected at some point (ANALYZE was run - see ANALYZE), but they may still be out of date
MAXKEYSIZE integer The maximum possible key size (in bytes) of this index.

Indexes

None

Example

In the following example, we are referencing the SCORING table created by the SQL QuickStart . This table has a primary key, SCORING..PRIMARY_KEY, which is defined over five columns as in the following output from SHOW TABLE HOCKEY.SCORING:

Primary Index: SCORING..PRIMARY_KEY on fields: PLAYERID, YEAR, STINT, TEAMID, POSITION
SELECT s.tablename, 
    s.indexname, 
    d.prefixlength, 
    d.distinctcount, 
    d.source FROM 
        system.indexstatistics s, 
        system.indexprefixstatistics d 
WHERE s.tablename='SCORING' AND 
    s.objectid = d.objectid AND 
    s.catalogid = d.catalogid 
ORDER BY s.tablename, s.indexname, d.prefixlength;
 TABLENAME       INDEXNAME       PREFIXLENGTH  DISTINCTCOUNT    SOURCE   
 ---------- -------------------- ------------- -------------- ---------- 
  SCORING   SCORING..PRIMARY_KEY       1            7357      statistics 
  SCORING   SCORING..PRIMARY_KEY       2           41158      statistics 
  SCORING   SCORING..PRIMARY_KEY       3           42379      calculated 
  SCORING   SCORING..PRIMARY_KEY       4           43601      calculated 
  SCORING   SCORING..PRIMARY_KEY       5           44823      statistics