Kbase 19394: Does -noindexhint Work For Every SELECT Command - Oracle?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/16/2008 |
|
Status: Verified
FACT(s) (Environment):
Oracle DataServer
FIX:
The Oracle DataServer adds hints to SELECT statements to help the performance of queries. You can suppress these hints using the -noindexhint client startup parameter and/or query-tuning switches.
The hint is added to the client SELECT statements (those generated for client queries) and to SELECT statements generated on the server that will SELECT <all columns> FROM <table> WHERE <progress_recid/rowid/unique-integer-column> = :rid.
The DataServer also adds a similar hint (on the server) for DELETE and UPDATE SQL statements (these always have a where clause like above).
The server side hints are done regardless of the -noindexhint, and/or query-tuning switches. These hints are thought to be ALWAYS useful, so they are not turned off. These hints do, however, rely upon accurate information in the schema holder regarding what is being used as a unique record identifer:
1) PROGRESS_RECID --> generate an INDEX hint on table-name##progress_recid, truncate index name using the same method protoora does.
2) ROWID --> generate a ROWID(T0) hint.
3) unique integer --> generate a hint on the index that has this as its one and only component. The dictionary requires that such an index exists (so it can be chosen to support recid). If the index is later removed, we could have a problem generating the hint.
So the -noindexhint startup parameter (and/or query-tuning switches) only applies to hints that the client generates. The hint on statements like "SELECT <all columns> FROM <table> WHERE progress_recid/rowid/unique-integer column > = :rid" will not be turned off.