Try OpenEdge Now
skip to main content
SQL Development
OpenEdge SQL Data Manipulation Language : Using indexes
 

Using indexes

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 OptimizingQuery Performance
* Index system catalog tables