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`
4 = `Unique Constraint`

FIELDCOUNT

integer

Number of fields specified in the index

INDEXID

bigint

An identifier for this index.

FLAGS

integer

Flag to indicate whether an index is enabled or disabled.

NOFLAGS = 0
DISABLED = 1
NULLS_ARE_EQUAL = 2
INCOMPLETE = 4
USINGMERGETREE = 8

The default value is (0). 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 256. Raising this number (see CREATE INDEX) will make more precise statistics available to the query optimizer.

IDENTIFIER

string

Persistent, autogenerated and unique identifier of the index; this identifier does not change when index is renamed.

Indexes

Primary Index: INDEXES..PRIMARY_KEY on fields: SCHEMA, INDEXNAME, TABLENAME
Unique Index: UNIQUE_INDEX_NAME on fields: SCHEMA, INDEXNAME
Secondary Index: INDEX_TABLE on fields: SCHEMA, TABLENAME
Secondary Index: SYSTEMINDEXIDENTIFIER on fields: IDENTIFIER

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        IDENTIFIER
 ------------------- ---------- ------- ---------- ----------- -------- ------ -------------------- ---------------------------------------

 HOCKEY..PRIMARY_KEY   HOCKEY   HOCKEY      0           1         79      0             256          HOCKEY..PRIMARY_KEY-47fbc5d7-01d0-b24e-69a5-c353ba004ad4
 PLAYER_IDX            HOCKEY   HOCKEY      1           3         80      0             256          PLAYER_IDX-d771ba44-20f1-b94c-1dae-dbab924635e0