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 Informix® 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 Informix® 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, Informix® 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 Informix® 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.
For example, suppose that automatic location is enabled for the current session when the following DDL statements are issued:
CREATE TABLE tab_autoloc (col1 INT);
CREATE INDEX ind_intab ON tab_autoloc (col1) IN TABLE;
Although no explicit storage option is specified for the tab_autoloc table, it has an implicit round-robin storage distribution, based on the AUTOLOCATE setting. Consequently, the CREATE INDEX statement fails, and no ind_intab index is registered in the system catalog, because in-table indexes cannot be created on fragmented tables.
Restriction: When you are defining an index on a table that was created with AUTOLOCATE enabled, if a round-robin storage distribution scheme was automatically defined for the table, you cannot use the IN TABLE keywords to specify nonfragmented storage for the index. That storage option is valid only for B-tree indexes on nonfragmented tables.
Informix® does not recommend use in new applications of the IN TABLE storage option, nor of the DEFAULT_ATTACH environment variable. Such indexes are a deprecated feature that might not be supported in some future release of Informix®.