Try OpenEdge Now
skip to main content
Database Essentials
Database Design : Defining indexes

Defining indexes

An index on a database table 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 including data entry, backup, and other common administration tasks. Each time you update an indexed column, OpenEdge updates the index, and related indexes as well. When you create or delete a row, OpenEdge updates each index on the affected tables.
As you move into the details of index design, remember 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 undermine an index scheme, and masterfully coded queries can perform poorly if not properly supported by indexes. Therefore, as your applications develop and evolve, your indexing scheme might need to evolve as well. The following sections discuss indexes in detail:
*Indexing basics
*Choosing which tables and columns to index
*Indexes and ROWIDs
*Calculating index size
*Eliminating redundant indexes
*Deactivating indexes
* Indexing basics
* Choosing which tables and columns to index
* Indexes and ROWIDs
* Calculating index size
* Eliminating redundant indexes
* Deactivating indexes