Progress
Database Design
Guide
Why Define an Index?
There are four significant benefits to defining an index for a table:
- Direct access and rapid retrieval of rows.
In Figure 4–1, the rows of the Order table are physically stored in the sequence the user enters them into the database. If you want to find a particular order number or order date, the system must scan every individual row in the entire table until it locates the row(s) that meet your selection criteria. Scanning is inefficient and time consuming where there are hundred thousands or millions of rows in the table.
However, if you create an index on the Order Number column, Progress then stores the order number values in sorted order (that is, 1, 2, 3, etc. ).
For example, when you query for order number 4, Progress does not go to the main table. Instead, it goes directly to the Order-Num index to search for this value. Progress 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/90). 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/90 to 09/20/90", 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. So, you can have multiple indexes on a table each providing a different sort ordering, the physical storage order is not controlled by 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 informing you 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 need not have the same name in both tables.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |