HP 3000 Manuals

ALLBASE/SQL Database Administration Guide

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:

CREATE [UNIQUE] [CLUSTERING] INDEX [Owner.]IndexName ON

[Owner.]TableName (ColumnName [ASC ] [,ColumnName [ASC ]] [...])
                              [DESC] [            [DESC]]
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.

MPE/iX 5.5 Documentation