DESCRIBE INDEX Statement

The DESCRIBE INDEX statement defines the specified index on a table.

Syntax

describe_index_statement ::=
    (DESCRIBE | DESC) [AS JSON] INDEX index_name ON table_name

For definition of the syntax component referenced in this statement, see:

table_name

Semantics

If you want the output to be in JSON format, you can specify the optional AS JSON.

The description for the index contains the following information:

Name of the table on which the index is defined.
Name of the index.
Type of index. Whether the index is primary index or secondary index.
Whether the index is multikey? If the index is multikey then ‘Y’ is displayed. Otherwise, ‘N’ is displayed.
List of fields on which the index is defined.
The declared type of the index.
Description of the index.

Example 8-17

The following statement provides information about the index idx_income1 on the UserInfo table.

DESCRIBE AS JSON INDEX idx_income1 ON UserInfo

Output:

{
  "name" : "idx_income1",
  "type" : "secondary",
  "fields" : [{
    "path" : "info.income",
    "type" : "AnyAtomic"
  }],
  "withNoNulls" : false,
  "withUniqueKeysPerRow" : false
}

Example 8-18

The following statement provides information about the index idx_phones on the UserInfo table.

DESCRIBE AS JSON INDEX idx_phones ON UserInfo

Output:

{
  "name" : "idx_phones",
  "type" : "secondary",
  "fields" : [{
    "path" : "info.phones[].number",
    "type" : "Integer"
  }],
  "withNoNulls" : false,
  "withUniqueKeysPerRow" : true
}

Example 8-19

The following statement provides information about the index idx_modtime on the users table.

DESCRIBE AS JSON INDEX idx_modtime ON users

Output:

{
  "name" : "idx_modtime",
  "type" : "secondary",
  "fields" : [{
    "function" : "modification_time",
    "type" : "Timestamp(3)"
  }],
  "withNoNulls" : false,
  "withUniqueKeysPerRow" : false
}

Example 8-20

The following statement provides information about the index idx_showid_year_month on the stream_acct table.

DESCRIBE AS JSON INDEX idx_showid_year_month ON stream_acct

Output:

{
  "name" : "idx_showid_year_month",
  "type" : "secondary",
  "fields" : [{
    "path" : "acct_data.contentStreamed[].showId",
    "type" : "Integer"
  },
  {
    "function" : "substring",
    "arguments" : "acct_data.contentStreamed[].seriesInfo[].episodes[].date AS String,0,4",
    "type" : "String"
  },
  {
    "function" : "substring",
    "arguments" : "acct_data.contentStreamed[].seriesInfo[].episodes[].date AS String,5,2",
    "type" : "String"
  }],
  "withNoNulls" : false,
  "withUniqueKeysPerRow" : false
}