Progress
Database Design
Guide
Overview
Like a book index, which helps a reader retrieve information on a topic quickly, a database table index speeds up the process of searching and sorting rows. Although it is possible to search and sort data without using indexes, indexes generally speed up data access. Use them to avoid or limit row scanning operations and to avoid sorting operations. If you frequently search and sort row data by particular columns, you might want to create indexes on those columns. Or, if you regularly join tables to retrieve data, consider creating indexes on the common columns.
On the other hand, indexes consume disk space and add to the processing overhead of many data operations—add, update, delete, copy, and move—including data entry, backup, and other common administration tasks. Each time you update an indexed column, Progress updates the index, and related indexes as well. When you create or delete a row, Progress updates each index on the affected tables.
As you move into the details of index design, keep in mind that index design is not a once-only operation. It is a process, and it is intricately related to your coding practices. Faulty code can thwart any index scheme, and masterfully coded queries perform poorly if not properly supported by indexes. Therefore, as the code develops, the indexing scheme—and other aspects of your database design—may have to evolve as well.
Fortunately, index creation, modification, and deletion are all simple operations. Typically, you create a database structure and a set of indexes that support expected access patterns. As the application code develops, new access patterns might arise that require index support, or particular queries might use indexes in unexpected ways, so you might have to modify indexes, query code, or both. Intelligent query coding is outside the scope of this manual, but its relevance to index design and creation cannot be overstated.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |