38.5.10 #INDEXES (displays index information)
- Function
-
This subcommand displays index information for the specified table.
- Format
-
#INDEXES [[schema-name.]table-identifier] [SHOW ALL];
- [[schema-name.]table-identifier]
-
Specifies the name of a table for which index information is to be displayed, in the format schema-name.table-identifier. If the schema name is omitted, the authorization identifier of the HADB user who executed this subcommand is assumed.
For details about the specification rules that apply when a schema name or table identifier includes any lowercase letter or backslash (\), see 1.4.3 Table name specification rules.
If the schema name and table identifier are omitted (a table name is not specified), index information for all tables is displayed. However, the tables whose information will be displayed vary depending on the privileges HADB users have. See Important points.
- [SHOW ALL]
-
If this option is specified
All columns in the SQL_INDEXES dictionary table are displayed. For details about the displayed information, see Content of SQL_INDEXES in the HADB Setup and Operation Guide.
When this option is omitted
Information shown in the following table is displayed.
Table 38‒5: Information displayed by #INDEXES (if the option is omitted) No.
Column name
Description
1
TABLE_SCHEMA
Schema name
2
TABLE_NAME
Table identifier of the table for which the index is defined
3
INDEX_NAME
- Index identifier
-
In the case of an index that corresponds to the primary key, an index identifier having the same name as the constraint name is stored.
4
INDEX_TYPE
Index type
-
B-TREE: B-tree index
-
RANGE: Range index
-
TEXT: Text index
5
COLUMN_COUNT
Number of indexed columns
6
UNIQUE_TYPE
B-tree index type (whether the index is unique)
-
Y: Unique index
-
N: Non-unique index
- Important points
-
-
The scope of index information displayed by executing #INDEXES varies depending on the privileges HADB users have. The following describes the scope of index information to be displayed.
- When the authorization identifier of an HADB user with the DBA privilege or audit admin privilege is used to connect to the HADB server
-
-
Indexes in tables defined by all HADB users
-
Indexes in tables whose schema name is MASTER
-
- When the authorization identifier of an HADB user without the preceding privilege is used to connect to the HADB server
-
-
Indexes in the tables defined by this HADB user (with the authorization identifier that was used for the current connection to the HADB server)
-
Indexes in tables for which access privileges have been granted by other HADB users
-
Indexes in tables permitted in the PUBLIC specification
-
Indexes in tables whose schema name is MASTER
-
The scope of index information displayed by executing #INDEXES is the same as the scope of information in the SQL_INDEXES dictionary table that can be referenced. For details, see Scope of information in dictionary tables that can be referenced by HADB users in Dictionary table overview in the HADB Setup and Operation Guide.
-
When #INDEXES is executed, the dictionary table is locked. Execute the COMMIT statement immediately after you execute #INDEXES. If you do not execute the COMMIT statement, the lock on the dictionary table will not be released. Note that when you terminate the adbsql command after executing #INDEXES, the lock on the dictionary table is released, so you do not need to execute the COMMIT statement.
-
If the specified schema name or table identifier does not exist, the execution result of #INDEXES contains zero rows (no error is generated).
-
The execution results of #INDEXES are output even if OFF (do not output search results) is specified in #SET DISPLAY. For details about #SET DISPLAY of the adbsql subcommand, see 38.5.12 #SET DISPLAY (outputs the retrieval results).
-
The #INDEXES execution time is not output even if #SET EXECTIME REPORT is set to ON (output SQL statement execution time). For details about #SET EXECTIME REPORT of the adbsql subcommand, see 38.5.14 #SET EXECTIME REPORT (outputs SQL statement execution time).
-
Even if #SET OPT REPORT is set to ON (display index, work table, and access path information), index information, work table information, and access path information are not output when #INDEXES is executed. For details about #SET OPT REPORT of the adbsql subcommand, see 38.5.16 #SET OPT REPORT (displays index, work table, and access path information).
-
The execution results of #INDEXES are output even if EXEC=PREPARE (do not execute the SQL statement) is specified in #SET OPT REPORT.
-