Kbase P122579: How to use oracle indexes not in the schema holder
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
09/09/2008 |
|
Status: Unverified
GOAL:
How to use oracle indexes not in the schema holder
GOAL:
Does progress use indexes not in the Schema Holder against Oracle?
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
WebSpeed 3.x
Oracle DataServer
Oracle 8.x
FIX:
Progress applications running against Progress DB chooses the indexes in compile time (except for dynamic queries), and when the application moves to Oracle, Progress DataServer in order to keep the similar behavior gives index hints to oracle trying to force Oracle to use the same index as Progress would use. This has benefits of having the sort order as if running against Progress. However, a drawback is that may deny a better index to be chosen, as well if you create an new index in Oracle database and not sync the schema holder, Progress applications may not take advantages of this index because a query that might use it, never knows about it and generates an index hint only for indexes referred in the schema holder.
In case the application needs to use an index not referred in the schema holder, progress provides a parameter to suppress the hints. It can used for the entire session (use it carefully, because could have side effect like totally different sort order) or you can use it per-query basis, suppressing the hint for an specific query.
It's important to point out that newer Oracle versions (9.x and newer) will not care about index hints and allways will choose the mostly appropriated index based on costs.
The usage forms are:
1 - session based
add -noindexhint to the startup parameters
2 - per query basis
FOR EACH customer, EACH order OF customer WHERE ord-num > 20
BY cust-num
QUERY-TUNING(no-index-hint):
end.
However, remember that hints may be ignored by Oracle due reasons out of the scope of this document.