Storage options
The storage options specify the distribution scheme of an index. You can use the IN clause to specify a storage space for the entire index, or you can use the FRAGMENT BY clause to fragment the index across multiple storage spaces.
- 1 See Storing an Index in a dbspace
- 2 See Storing Data in an extspace
- 3 B-tree indexes on nonfragmented tables only
- 4 See Creating an Index with the IN TABLE Keywords
- 5 See FRAGMENT BY Clause for Indexes
| Element | Description | Restrictions | Syntax |
|---|---|---|---|
| dbspace | The dbspace in which to store the index | Must exist | Identifier |
| extspace | Name assigned by the onspaces command to a storage area outside the database server | Must exist | See the documentation for your access method. |
If you specify any storage option (except IN TABLE), you create a detached index. Detached indexes are indexes that are created with a specified distribution scheme. Even if the distribution scheme specified for the index is identical to that specified for the table, the index is still considered to be detached. If the distribution scheme of a table changes, all detached indexes continue to use the distribution scheme that the Storage Option clause specified.
If you do not include the Storage Option clause, by default the index is created in the same dbspace as the corresponding table.
If you do not include the Storage Option clause, by default an attached index is created in the same dbspaces as the corresponding table fragments. However, if automatic location is enabled, an index created on a round-robin table is detached by default in a single fragment, located in a dbspace chosen by the server. You enable automatic location by setting the AUTOLOCATE configuration parameter or session environment option to a positive integer.
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.