Try OpenEdge Now
skip to main content
Database Essentials
Database Design : Defining indexes : Indexing basics : Reasons for defining an index
Reasons for defining an index
There are four benefits to defining an index for a table:
*Direct access and rapid retrieval of rows.
The rows of the tables are physically stored in the sequence the users enter them into the database. If you want to find a particular row, the database engine must scan every individual row in the entire table until it locates one or more rows that meet your selection criteria. Scanning is inefficient and time consuming, particularly as the size of your table increases. When you create an index, the index entries are stored in an ordered manner to allow for fast lookup.
For example, when you query for order number 4, OpenEdge does not go to the main table. Instead, it goes directly to the Order-Num index to search for this value. OpenEdge uses the pointer to read the corresponding row in the Order table. Because the index is stored in numerical order, the search and retrieval of rows is very fast.
Similarly, having an index on the date column allows the system to go directly to the date value that you query (for example, 9/13/04). The system then uses the pointer to read the row with that date in the Order table. Again, because the date index is stored in chronological order, the search and retrieval of rows is very fast.
*Automatic ordering of rows.
An index imposes an order on rows. Since an index automatically sorts rows sequentially (instead of the order in which the rows are created and stored on the disk), you can get very fast responses for range queries. For example, when you query, "Find all orders with dates from 09/6/04 to 09/20/04," all the order rows for that range appear in chronological order.
Note: Although an index imposes order on rows, the data stored on disk is in the order in which it was created. You can have multiple indexes on a table, each providing a different sort ordering, and the physical storage order is not controlled by either of the indexes.
*Enforced uniqueness.
When you define a unique index for a table, the system ensures that no two rows can have the same value for that index. For example, if order-num 4 already exists and you attempt to create an order with order-num 4, you get an error message stating that 4 already exists. The message appears because order-num is a unique index for the order table.
*Rapid processing of inter-table relationships.
Two tables are related if you define a column (or columns) in one table that you use to access a row in another table. If the table you access has an index based on the corresponding column, then the row access is much more efficient. The column you use to relate two tables does not need to have the same name in both tables.