Table Index Element
Function
Specifies a set of columns as an index.
Invocation
This element is part of the table element. The short-name identifier is only allowed in a CREATE TABLE DESCRIPTION or a CREATE CLUSTER DESCRIPTION statement.
Syntax
[UQINDEX | [HAVING | UNIQUE] INDEX ] [index_identifier] (column_identifier [(start_pos-end_pos)][,]) [short_name]
where:
index_identifier | |
column_identifier | Name of a column to be used in the forming of an index. |
start_position | Start position within the column when defining an Adabas descriptor. |
end_position | End position within the column when defining an Adabas descriptor. The end position must be greater than the start position. |
shortname_identifier | Adabas short name. |
Descripton
In order to improve an existing application's performance, establish an index for one or more base table column(s).
You can create an Adabas descriptor that reflects the capabilities of the Adabas database system's descriptors definition when you have the table index element. For a detailed discussion of Adabas descriptors, please refer to the Adabas documentation for your environment.
A range specification is when start and end positions are specified. This allows an index specification to be restricted to sub-elements of a column.
The following conventions hold true for the explanations below:
Let Cl = ( c1,... , cn ) be a column list of one or more columns for which this table index element is specified.
Let T be the table where the columns of Cl resides.
INDEX:
A INDEX is used to allow more efficient base table access. The index is based on one or more column(s) of a base table, when the listed columns' are considered as an entity.
An Adabas Superdescriptor will be generated if the number of columns in the column list is greater than one.
An Adabas Descriptor will be added if the index is in a single column with no range specified.
An Adabas Subdescriptor will be generated if a range specification is in a single column.
HAVING UNIQUE INDEX:
This feature is provided for compatibility and will be removed in future versions. Use a UNIQUE constraint instead.
UQINDEX:
A UQINDEX is an index that generates a unique Adabas sub- or superdescriptor on a sub-table column. SQL does not conisder this descriptor to be unique; it cannot be represented by a normal "unique constraint."
Limitations
The shortname identifier is only used in a CREATE TABLE DESCRIPTION or a CREATE CLUSTER DESCRIPTION statement.
A specification of a UQINDEX is only valid for level 1 and level 2 base tables (subtables).
You are not allowed to specify a UNIQUE INDEX together with a UNIQUE constraint or a PRIMARY KEY.
When using a HAVING UNIQUE INDEX in conjunction with a SUPPRESSION attribute the attribute NOT NULL is not permitted.
When using a HAVING UNIQUE INDEX in conjunction with a DEFAULT Adabas attribute, the attribute NULL is not permitted.
ANSI Specifics
The table index element is not part of the Standard.