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
INDEXTYPE string The type of index
STATSTYPE string The type of statistics: Ver1 for version 1 statistics and Ver2 for version 2 statistics
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.
MINKEY string The minimum key in the index statistics
MAXKEY string The maximum key in the index statistics

Indexes

None

Examples

Example 1

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 

Example 2

SQL> select * from system.indexstatistics where tablename='tables';


OBJECTID  CATALOGID  SCHEMA  TABLENAME       INDEXNAME        INDEXTYPE   STATSTYPE  KEYCOUNT  VALID  MAXKEYSIZE            MINKEY                    MAXKEY
--------- ---------- ------- ---------- ------------------- ------------- ---------- --------- ------ ----------- --------------------------- ----------------------


    1         6      SYSTEM    TABLES   TABLES..PRIMARY_KEY TwoLevelBTree    Ver2       36      TRUE     1024     [ALLSYSTEMFIELDS], [SYSTEM] [VIEW_TABLES], [SYSTEM]

    6         6      SYSTEM    TABLES   TABLES_ST           TwoLevelBTree    Ver2       36      TRUE     1024     [SYSTEM], [ALLSYSTEMFIELDS] [SYSTEM], [VIEW_TABLES]