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 statement:

   isql=> CREATE UNIQUE INDEX AlbCodeIndex Return

   > ON ALBUMS (AlbumCode); Return

Use the following statement 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 statement in the "SQL Statements" chapter of the ALLBASE/SQL Reference Manual.