B.5 Content of SQL_INDEXES

1

TABLE_SCHEMA

VARCHAR(100)

Schema name

2

TABLE_NAME

VARCHAR(100)

Table identifier of the table for which the index is defined

3

INDEX_NAME

VARCHAR(100)

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_ID

SMALLINT

Index ID:

• From 0x00030001: Indexes of dictionary tables (base tables)

• From 0x00030191: Indexes of system tables (base tables)

• From 0x00030321: Indexes of base tables defined by HADB users

5

TABLE_ID

SMALLINT

Table ID of the table for which the index is defined:

• From 0x00020001: Dictionary table (base table)

• From 0x000200C9: System table (base table)

• From 0x00020191: Base tables defined by HADB users

6

COLUMN_COUNT

SMALLINT

Number of indexed columns

7

COLUMN_ID_LIST

VARCHAR(32)

List of indexed column IDs^#

8

CREATE_TIME

TIMESTAMP

Datetime stamp for when the index was defined

9

UNIQUE_TYPE

CHAR(1)

B-tree index type (whether the index is unique):

• 'Y': Unique index

• 'N': Non-unique index

10

FREE_AREA

SMALLINT

Percentage of unused page area in an index.

For a range index, the null value is stored.

11

INDEX_KEY_EXPAND

CHAR(1)

Whether the sum total of the sizes of the columns comprising the index is at least 256 bytes:

• 'Y'

  The sum total of the sizes of the columns comprising the index is at least 256 bytes.

• 'N'

  The sum total of the sizes of the columns comprising the index is 255 bytes or less.

12

INDEX_TYPE

CHAR(1)

Index type:

• 'B': B-tree index

• 'T': Text index

• 'R': Range index

13

IS_PRIMARY_KEY

CHAR(1)

Whether an index corresponds to the primary key

• 'Y'

  Index that corresponds to the primary key

• Null value

  Index that does not correspond to the primary key

The null value is also stored for indexes of dictionary tables (base tables) and indexes of system tables (base tables).

14

IS_CHUNK_SKIP

CHAR(1)

Whether a range index can skip chunks

• 'Y'

  Range index that can skip chunks

• Null value

  Range index that cannot skip chunks

The null value is stored for the following indexes:

• Indexes other than range indexes

• Range indexes that have been defined by a version earlier than 02-02

15

IS_EXCLUDE_NULL_VALUES

CHAR(1)

Whether null-value exclusion is specified

• 'Y'

  Index for which null-value exclusion is specified

• Null value

  Index for which null-value exclusion is not specified

16

IS_ARCHIVE_RANGE

CHAR(1)

Is range index defined automatically by HADB server?

• 'Y'

  Range index defined for the archive range column automatically by HADB server

• Null value

  Range index that does not meet the conditions of 'Y'

However, if an archivable multi-chunk table is changed to a regular multi-chunk table, a null value is stored even for range indexes automatically defined by the HADB server.

A null value is also stored for the following indexes:

• Indexes of dictionary tables (base tables)

• Indexes of system tables (base tables)

17

IS_TEXT_CORRECTION_RULE

CHAR(1)

Is there a notation-correction-search text-index specification?

• 'Y'

  Text index that has a notation-correction-search text-index specification

• Null value

  Text index that does not have a notation-correction-search text-index specification

A null value is also stored for the following indexes:

• Indexes of dictionary tables (base tables)

• Indexes of system tables (base tables)

• Indexes other than text indexes

18

IS_TEXT_WORDCONTEXT

CHAR(1)

Whether there is a text-index-word-context search specification

• 'Y'

  Text index that has a text-index-word-context search specification

• Null value

  Text index that does not have a text-index-word-context search specification

A null value is also stored for the following indexes:

• Indexes of dictionary tables (base tables)

• Indexes of system tables (base tables)

• Indexes other than text indexes

19

TEXT_DELIMITER_TYPE

CHAR(1)

Type of text-index delimiter specification

• 'D'

  Text index for which DEFAULT is specified as the text-index delimiter specification (including those whose text-index delimiter specification was omitted causing DEFAULT to be assumed)

• 'A'

  Text index for which ALL is specified as the text-index delimiter specification

A null value is stored for the following indexes:

• Text index for which no text-index-word-context search specification is specified

• Indexes of dictionary tables (base tables)

• Indexes of system tables (base tables)

• Indexes other than text indexes