An index is a database object that enables quick information retrieval from a table. OpenEdge SQL employs a B-tree index, which organizes data in ascending or descending order. For every entry in the index table there is a corresponding entry in the database table to which it is associated. This speeds a query because it is more efficient to locate a row by searching a sorted index than by searching an unsorted table. This type of index is ideal for searching for a single value or a range of values.

Create an index when:

  • The column is commonly used in a WHERE clause or in a join condition.
  • The column contains a large number or a wide range of values.
  • Two or more columns are frequently used together in a WHERE clause or a join condition.
  • The table is large and most queries are expected to retrieve less than a small percentage of rows.

Do not create an index if:

  • The table is frequently updated.
  • Most queries retrieve a large percentage of the rows in a table.
  • The columns are not frequently used as a condition of a query.

To create or drop index information, use the CREATE INDEX and DROP INDEX statements. For information on working with indexes to optimize query performance, see Optimize query performance