Creating Indexes

ALLBASE/SQL uses the data in the columns as a key to facilitate data retrieval. Use the CREATE INDEX statement as in the following syntax:

The index name must conform to ALLBASE/SQL naming conventions explained in the ALLBASE/SQL Reference Manual. If you do not specify an owner name, then the index name must be unique throughout the DBEnvironment. Refer to the ALLBASE/SQL Reference Manual for complete syntax and semantics.

There are several indexes in the sample database. The following examples show how to create each type. The first is a nonunique, nonclustering index:

   isql=>  CREATE INDEX VendPartIndex

   > ON PurchDB.SupplyPrice (VendorNumber);

The next example shows the creation of a unique index:

   isql=>  CREATE UNIQUE INDEX PartNumIndex

   > ON PurchDB.Parts (PartNumber);

The following shows the creation of a clustering index:

   isql=>  CREATE CLUSTERING INDEX PartToNumIndex

   > ON PurchDB.SupplyPrice (PartNumber);

For details on how ALLBASE/SQL uses each type of index, refer to the "Logical Design" and "Maintenance" chapters.

	NOTE: Before you build a large index, it is advisable to use the CREATE TEMPSPACE statement to designate an area where the system can open temporary files that are large enough for the sorting that is required. By default, the current group is used as a tempspace. If there is not enough space in the current group, the CREATE INDEX statement may fail for large indexes, unless alternate tempspaces are designated. Refer to "Physical Design" chapter for more information about creating tempspaces. Also refer to the "CREATE TEMPSPACE" statement in the ALLBASE/SQL Reference Manual.