Kbase P127720: What is the impact of the USE-INDEX on a FIND query against ORACLE
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
18/12/2007 |
|
Status: Unverified
GOAL:
What is the impact of the USE-INDEX on a FIND query against ORACLE
GOAL:
What does the USE-INDEX clause do in a FIND query using the DataServer for ORACLE
FACT(s) (Environment):
Oracle DataServer
FIX:
The results of a FIND statement are always ordered but the order cannot be guaranted unless an order is selected using the USE-INDEX clause - the consequences of which may be costly in performance.
USE-INDEX affects the SQL ORDER BY clause based on the components of the selected index and also becomes the sort order for the FIND cursor.
By not specifying the USE-INDEX clause, the optimizer is free to retrieve the results in whatever order is most efficient to satisfy the WHERE clause bracketing. The FIND cursor's sort order is selected by the optimizer instead. The optimizer is free to ignore the hint and does not need to develop a query plan that abides by it if another query plan is more efficient.