Updates in the 10.1B OpenEdge database enable existing index key limits, currently set at approximately 200 bytes, to be widened. For example, this enhancement enables databases with a block size of 4K or 8K to support a maximum index key of approximately 2000 bytes. Also, the index key width expansion extends the maximum key size supported in the OpenEdge client that can be used with databases exported to foreign data sources.
When you are migrating OpenEdge keys to a foreign data source, the key sizes cannot be larger than those supported by the foreign data source. Because the maximum key size is data-source dependent, you should consider your target data source's capacity with respect to the maximum key-size capacity before you perform a migration.
Indexes allow you to use the OF keyword in ABL with FOR EACH and FIND statements. Using the OF keyword improves the readability of your code. The OF keyword is a shorter version of a WHERE clause. You can use OF only when you have a field of the same name in two tables and this field is a unique index in at least one of the tables. You can then write the following statement:
FOR EACH customer OF order:
Index definitions support ABL USE-INDEX modifier. ABL translates USE-INDEX to ORDER BY for DataServer operations. For example, if you define city-dept as an index on the city and department fields without using the single shot query mechanism, the following ABL statements are equivalent when accessing an Oracle database:
FOR EACH employee USE-INDEX city-dept:
FOR EACH employee BY employee.city BY employee.department:
The following examples illustrate the behavior of the index definition if the single-shot query mechanism is used.
If single-shot query mechanism is used, the following query results in a server-side join:
FOR EACH customer NO-LOCK USE-INDEX custnum WHERE custnum = 1,
FIRST order OF customer USE-INDEX custorder:
If the single-shot query mechanism is used, the following query results in a client-side join
FOR EACH customer NO-LOCK WHERE custnum = 1,
FIRST order OF customer
BY customer.custnum BY order.custnum BY order.ordernum:
If you do not specify USE-INDEX, 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 in your query definition.
Progress recommends that you use a unique index as a USE-INDEX modifier when using it with a FOR EACH query that joins FIRST or LAST on a child table. If the index is not unique, then DataServer determines the FIRST or LAST of the joined table by appending the ROWID key to the USE-INDEX key components. However, ROWID key is not appended if NO-QUERY-UNIQUE-ADDED is specified in the QUERY-TUNING parameter of the FOR EACH query making the FIRST or LAST criteria unpredictable.
Oracle chooses which index, if any, to use when the OpenEdge application accesses information in the Oracle database. However, the DataServer passes an index hint to Oracle that specifies the index to use for a query and in which order to read the index. The hints take the form of comments in the SQL code generated by the DataServer.
The DataServer issues index hints to Oracle according to two guidelines:
1. If you use ABL USE-INDEX modifier in your code, the DataServer generates a hint telling Oracle which to use. The DataServer considers the direction of your query and whether you declared the first component of your index to be ascending or descending. The DataServer then issues an SQL statement to Oracle that it should read the index either forward or backward to ensure that it retrieves the records in the order you specified.
By including the USE-INDEX modifier in your ABL code, you can enhance Oracle performance, especially in cases where your application returns records in a descending order.
2. If you do not use the USE-INDEX modifier, the DataServer might generate an index hint based upon the WHERE or BY option. If the WHERE clause has one of the following elements, the DataServer generates an index hint based on the BY option:
The not equal operator (< >)
A function
An expression,
For example, the DataServer passes an index hint to Oracle to use custnum for the following query:
FOR EACH customer WHERE customer.address = "55 Cambridge"
BY customer.custnum:
If you issue a query that includes BY options, the DataServer considers whether the fields for the BY option participate in a compound index and generates an index hint to Oracle to use that index if the WHERE clause does not imply a different index.
You can prevent the DataServer from passing hints to Oracle by using the NO-INDEX-HINT option for the QUERY-TUNING phrase or by using the -noindexhint startup parameter. See Query tuning and Oracle hints for more information.
Note: An ABL INDEX-INFORMATION cannot be used against the Oracle DataServer. The DataServer does not inform ABL which index or indexes Oracle uses to perform a query, therefore this attribute does not contain any valid information.
The not equal operator (< >)