Database index key widths

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:

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.