Progress
DataServer
for ORACLE Guide
Indexes
You create and maintain all indexes from within ORACLE. When you create a schema image from a target ORACLE database, Progress automatically copies the ORACLE index definitions.
Indexes allow you to use the OF keyword in Progress 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:
Index definitions support the Progress 4GL USE-INDEX modifier. Progress 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, the following Progress statements are equivalent when accessing an ORACLE database:
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.ORACLE chooses which index, if any, to use when the Progress 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:
- If you use the Progress 4GL 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 a 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 Progress 4GL code, you enhance ORACLE performance, especially in cases where your application returns records in a descending sequence.
- If you do not use the Progress 4GL 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 cust-num for the following query:
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 the "Query Tuning" and "ORACLE Hints" sections for more information.
Leading and Trailing Spaces
Progress and ORACLE handle leading or trailing blanks in an indexed column differently. For example, when you attempt to create a record in ORACLE and include leading or trailing blanks in a column that participates in a unique index, ORACLE returns a message that there is a duplicate unique key. To address this, the DataServer, by default, trims leading or trailing blanks when you use them in a WHERE clause. If you want to specify leading or trailing blanks, specify the -znotrim startup parameter when you start the Progress client session.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |