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

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
}