Up and Running with ALLBASE/SQL: HP 3000 and HP 9000 Computer Systems > Chapter 3 Setting Up a Database with ISQLCreating 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:
Use the following statement to create a non-unique index on the AlbumCode column of the Titles table:
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.
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. |