INDEXES System Table Description

Description

Information about all the indexes on persistent tables in the database.

Fields

Field Type Description
INDEXNAME string Name of the index (this may be NuoDB-generated)
TABLENAME string Name of the table containing the index
SCHEMA string Name of the schema containing the table containing the index
INDEXTYPE integer

Index type:

  • 0 = Primary Key
  • 1 = Unique Index
  • 2 = Secondary Index
  • 3 = Foreign Key Index
FIELDCOUNT integer Number of fields specified in the index
INDEXID integer A unique identifier for this index.
FLAGS integer Flag to indicate whether an index is enabled or disabled. Default value is zero(0), which indicates that index is enabled. See ALTER TABLE command to enable/disable an index.
HISTOGRAMRESOLUTION integer The number of histogram buckets created for the index. Index histograms are one of the most important parts of index statistics. By default, the number of histogram buckets created is 10. Raising this number (see CREATE INDEX) will make more precise statistics available to the query optimizer.

Indexes

Primary Index: INDEXES..PRIMARY_KEY on fields: SCHEMA, INDEXNAME, TABLENAME
Secondary Index: INDEX_TABLE on fields: SCHEMA, TABLENAME

Example

In the following example, we are referencing the table HOCKEY created by SQLQuickStart (see Running the SQL QuickStart). Here we see there are two indexes. One has an index name that is generated from the declaration of a primary key. The other index is a named index called PLAYER_IDX and it is defined on three fields; it is created by the SQL QuickStart with the command:

CREATE UNIQUE INDEX player_idx ON hockey (number, name, team);

SELECT * FROM system.indexes WHERE tablename='HOCKEY';

      INDEXNAME      TABLENAME  SCHEMA  INDEXTYPE  FIELDCOUNT  INDEXID  FLAGS  HISTOGRAMRESOLUTION  
 ------------------- ---------- ------- ---------- ----------- -------- ------ -------------------- 

 HOCKEY..PRIMARY_KEY   HOCKEY   HOCKEY      0           1         79      0             10          
 PLAYER_IDX            HOCKEY   HOCKEY      1           3         80      0             10