HP 3000 Manuals

Up and Running with ALLBASE/SQL

Creating an Index 

You can speed up access to data by providing indexes on specific columns
in your tables.  Assuming your tables are large enough, an index scan 
will arrive at a specific row more quickly than a serial scan.  When an
index is used, ALLBASE/SQL looks for an entry in the index first, then
goes to the row.  When a serial scan is used, ALLBASE/SQL reads from the
beginning of the table until the desired row is reached.  Naturally, the
use of the index is faster if you only need a small subset of rows.

You can also use an index to guarantee the uniqueness of specific column
values.  In the Albums table, for example, the AlbumCode column should be
unique; in the Titles table, it should not be unique, because a single
album may contain several selections.

Create a unique index on the AlbumCode column of the Albums table with
the following command:

     isql=> CREATE UNIQUE INDEX AlbCodeIndex Return 
     > ON ALBUMS (AlbumCode); Return 

Use the following command to create a non-unique index on the AlbumCode
column of the Titles table:

     isql=> CREATE INDEX TitleCodeIndex Return 
     > ON TITLES (AlbumCode); Return 

Location of Tables and Indexes 

Each index is created in the same DBEFileSet as the table it is indexing.
These two indexes are created in the DBEFileSet ALBUMFS, and they are
physically located in the INDEX DBEFile created for that DBEFileSet in an
earlier step.

As shown in the illustration,
indexes and tables always appear in the same DBEFileSet (shelf area).
They may be in different DBEFiles, however.


            


NOTE  When you issue a query, you do not tell ALLBASE/SQL to use an
      index.  Instead, the SQLCore optimizer decides when the use of an
      existing index is the best way to access a specific set of data.


For more information about indexes, refer to the discussion of "Providing
Data Access Paths" in the chapter "Using ALLBASE/SQL" of the ALLBASE/SQL 
Reference Manual.  Also, see the CREATE INDEX command in the "SQL
Commands" chapter of the ALLBASE/SQL Reference Manual.

MPE/iX 5.0 Documentation