IN Clause

Use the IN clause to specify a storage space to hold the entire index. The storage space that you specify must already exist.

Storing an Index in a dbspace

Use the IN dbspace clause to specify the dbspace where you want your index to reside. When you use this clause with any option except the TABLE keyword, you create a detached index.

The IN dbspace clause allows you to isolate an index. For example, if the customer table is created in the custdata dbspace, but you want to create an index in a separate dbspace called custind, use the following statements:

CREATE TABLE customer
   . . .
   IN custdata EXTENT SIZE 16
 
CREATE INDEX idx_cust ON customer (customer_num) IN custind

Storing an Index Fragment in a Named Partition (IDS)

Besides the option of storing a fragment of the index in a dbspace, Dynamic Server supports storing fragments of the index in a named subset of the dbspace, called a partition. Unless you explicitly declare names for the fragments in the PARTITION BY or FRAGMENT BY clause, each fragment, by default, has the same name as the dbspace where it resides. This includes all fragmented tables and indexes migrated from earlier releases of Dynamic Server.

Storing an Index in a dbslice (XPS)

Using Extended Parallel Server, the IN dbslice clause allows you to fragment an index across multiple dbspaces. The database server fragments the table by round-robin in the dbspaces that make up the dbslice when the table is created.

Storing Data in an extspace (IDS)

In general, use the extspace storage option in conjunction with the USING Access-Method Clause (IDS). For more information, refer to the user documentation for your custom-access method.

Creating an Attached Index with the IN TABLE Keywords (IDS)

In some earlier releases of Dynamic Server, if you did not use the storage options to specify a distribution scheme, then, by default, the index used the same distribution scheme as the table on which it was built. Such an index is called an attached index. If you omit the Storage Options clause, Dynamic Server creates new indexes as detached indexes by default, but supports existing attached indexes created by earlier release versions. (The DEFAULT_ATTACH environment variable, however, can override the default behavior so that the new index is attached.)

You can also specify IN TABLE as the storage option to create an attached index, even if the DEFAULT_ATTACH environment variable is not set. An attached index is created in the same dbspace (or dbspaces, if the table is fragmented) as the table on which it is built. If the distribution scheme of a table changes, all attached indexes start using the new distribution scheme.

Only B-tree indexes that are nonfragmented and that are on nonfragmented tables can be attached. All other indexes, including extensibility related indexes, such as R-trees and functional indexes, must be detached. You cannot create an attached index using a collating order different from that of the table, nor different from what DB_LOCALE specifies. For information about the DB_LOCALE and DEFAULT_ATTACH environment variables, see the IBM Informix Guide to SQL: Reference.