INDEXSTATISTICS System Table Description

Description

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

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

INDEXNAME

string

The name of the index

OBJECTID

bigint

The ID for the database object

CATALOGID

bigint

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

INDEXIDENTIFIER

string

The persistent, automatically generated, and unique identifier 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

COLLECTIONENABLED

boolean

True if automatic statistics collection is currently enabled on this table. See ANALYZE for information about automatic statistics collection.

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';

     INDEXNAME      OBJECTID  CATALOGID  SCHEMA  TABLENAME    INDEXIDENTIFIER     INDEXTYPE   STATSTYPE  KEYCOUNT  VALID  MAXKEYSIZE            MINKEY                    MAXKEY          COLLECTIONENABLED
------------------- --------- ---------- ------- ---------- ------------------- ------------- ---------- --------- ------ ----------- --------------------------- ----------------------- ------------------
TABLES..PRIMARY_KEY     1         58     SYSTEM    TABLES   TABLES..PRIMARY_KEY TwoLevelBTree    Ver2       36      TRUE     1024     [ALLSYSTEMFIELDS], [SYSTEM] [VIEW_TABLES], [SYSTEM]        TRUE
TABLES_ST               2         58     SYSTEM    TABLES   TABLES_ST           TwoLevelBTree    Ver2       36      TRUE     1024     [SYSTEM], [ALLSYSTEMFIELDS] [SYSTEM], [VIEW_TABLES]        TRUE