Progress
Database Design
Guide
Indexes
An index is a “b-tree” that corresponds with a data table. For every row in the data table there is a corresponding row in the index table. Records in the index are always sorted.
The best time to create an index is the same time you are creating the table. It is important to anticipate the ways in which you may want to access the data and create indexes to accommodate that.
A primary index is an index on a primary key. A secondary index is an index on a key that is not the primary key in the table. An index can be unique.
An index has these advantages:
- Faster row search and retrieval. It is more efficient to locate a row by searching a sorted index table than by searching an unsorted table.
- Records are ordered automatically to support your particular data access patterns. No matter how you change the table, when you browse or print it, the rows appear in indexed order instead of their stored physical order on disk.
- When you define an index as unique, each row is unique. This ensures that duplicate rows do not occur.
- A combination of columns can be indexed together to allow you to sort a table in several different ways at once (for example, sort the Projects table by a combined employee and date column).
- Efficient access to data in multiple related tables.
To create an index in SQL-92 use the CREATE INDEX statement. This statement will create an index on the table you specify using the specified columns of the table. For more information on the CREATE INDEX statement see the Progress SQL-92 Guide and Reference.
You may create a unique index by creating the index on the primary key. A unique index does not allow the table to contain any rows with duplicate column values for the set of columns specified for the index.
A secondary index may be created on a key that is not primary.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |