Kbase 20783: Join Does Not Generate Index Hint to ORACLE
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/2/2008 |
|
Status: Verified
GOAL:
Does a Join of two or more statements through Oracle DataServer generate an Index Hint to ORACLE?
FACT(s) (Environment):
Oracle DataServer
FIX:
A Join of two or more tables through Oracle DataServer does not send an index hint to ORACLE
When referencing no-joint queries, the DataServer generates an index hint to ORACLE based on the information stored in the schema holder. This leaves it up to ORACLE to decide which index to use to resolve the joint query.
The DataServer usually defines the order of result set based on the USE-INDEX statement, when the statement is used. At these times, DataServer ORACLE recognizes the fields in the index, and sends the order of result set in the ORDER BY clause. Make sure you specify USE-INDEX if you need to guarantee the result set order.
For reference and testing, use -Dsrv qt_debug,sql at the ORACLE database connection. It outputs the SQL statements that are passed to ORACLE within the dataserv.lg file. The log file contains the SELECT that is passed from Progress to ORACLE by the DataServer.
To force ORACLE to use an index, enter the QUERY-TUNING HINT option. Be aware that passing incorrect hint syntax, inappropriate hints, or conflicting hints does not return an error but might give you unpredictable results.
By using -Dsrv qt_debug,sql, user will see each SELECT statement that is passed using the hint, SELECT /*+ + INDEX.