Creating an Index with the IN TABLE Keywords

Specifying IN TABLE as the storage option creates an index where both the index and the data pages for its table are stored together in the same extents, and the dbspace distribution scheme for the index is the same as that of the table on which it was built.

Using IN TABLE as the storage option specifies the same storage design for non-fragmented B-tree indexes as enabling the DEFAULT_ATTACH environment variable, but both DEFAULT_ATTACH and the IN TABLE keywords are deprecated features.

The name of the DEFAULT_ATTACH environment variable preserves an obsolete definition of the term attached index. In current HCL OneDB™ nomenclature, this term now designates an index whose data pages are stored in separate tablespaces and separate extents from the data pages of the table, but the index and its table share the same dbspace distribution scheme. For more information, see the description of DEFAULT_ATTACH in the HCL OneDB Guide to SQL: Reference.

The following restrictions apply to the IN TABLE keywords as an index storage option:

• If the table on which you define the index is a fragmented table, HCL OneDB issues errors -212 and -130 if you specify the IN TABLE option.
• You cannot apply the IN TABLE storage option to forest of trees indexes.
• This option does not support extensibility-related indexes, such as R-tree indexes, functional indexes, or indexes that DataBlade® modules provide.
• You cannot specify this storage option for any index that uses a collating order different from that of its table, nor different from what the DB_LOCALE setting specifies. For more information about the DB_LOCALE environment variable, see the HCL OneDB Guide to SQL: Reference.
• You cannot apply the IN TABLE storage option to indexes on tables implicitly fragmented by the AUTOLOCATE configuration parameter or by the SET ENVIRONMENT AUTOLOCATE session environment option.

CREATE TABLE tab_autoloc (col1 INT);
CREATE INDEX ind_intab ON tab_autoloc (col1) IN TABLE;