Indexes and sorting

You create and maintain all indexes from within the ODBC data source, using native data-source tools rather than the Data Dictionary. A data-source index uses a logical pointer to the physical locations of table rows in order to sequence data access. You can add and drop indexes but you cannot use their names in queries. The data source alone ultimately decides when and how to use indexes; its decisions are not affected by the DataServer.

Using index definitions in the ODBC data source, the DataServer builds index information in the schema holder. OpenEdge index definitions for the data source schema serve two purposes:

They allow you to use the OF option in OpenEdge ABL with the FOR EACH and FIND statements. Using the OF option improves the readability of your code. The OF keyword is equivalent to the SQL WHERE clause. You can use OF only when you have a field of the same name in two tables and the field is an index in at least one of the tables. Therefore, since the custnum field is common to both the order and customer tables, you could write the following statement:

FOR EACH order OF customer:

They support the ABL USE–INDEX option. USE–INDEX is translated to SQL ORDER BY for DataServer operations. For example, if you define city-dept as an ODBC data-source primary key on the city and department fields, it is a unique index in the schema holder. In this case, the following ABL statements are equivalent when accessing the data source:

FOR EACH employee USE-INDEX city-dept:

FOR EACH employee BY employee.city BY employee.department:

Note: If you do not specify USE–INDEX or ORDER–BY, your query will return records in an unpredictable order. Your application might not require predictable ordering, but if it does, be sure to include USE–INDEX or ORDER–BY in your query definition.

In this section:

USE-INDEX and BY clause considerations

Dummy indexes for sort order

Unique indexes

Large key entry support