| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Hint Help!
On Fri, 23 May 1997 10:11:09 -0500, Brad Skiles <bwskiles_at_adpc.purdue.edu> wrote:
>I'm having trouble getting Oracle to recognize an INDEX hint, and I'm
>hoping someone out there can tell me what's going on. The query runs
>best when using the index on the DEPT field, as I know from using the
>RULE hint, and as you can tell by comparing the times of the 2 following
>queries. I'd like to just tell the optimizer which index to use, but it
>seems to ignore my request. Below is the same query with 1) the index
>hint and 2) the RULE hint.
>
>First, here's the query I want to run and the ensuing explain plan:
>
>SELECT /*+ INDEX(TRANSACTION TRANSACT_DEPT) */
> AL9.FUND,
> AL9.PROJ,
> AL9.OBJ,
> AL9.AMOUNT,
> AL9.DEPT
>FROM
> TRANSACTION AL9
^^^^
Since you used the correlation name, the table TRANSACTION doesn't exist outside
of the FROM clause. The hint would be /*+ INDEX(AL9 TRANSACT_DEPT) */
From the server tuning manual for example:
<quote>
FULL
The FULL hint explicitly chooses a full table scan for the specified table.
The syntax of the FULL hint is
FULL(table)
where table specifies the name or alias of the table on which the full table
scan is to be performed.
For example, Oracle performs a full table scan on the ACCOUNTS table
to execute this statement, even if there is an index on the ACCNO
column that is made available by the condition in the WHERE clause:
SELECT /*+ FULL(a) Don’t use the index on ACCNO */ accno, bal
FROM accounts a
WHERE accno = 7086854;
Note: Because the ACCOUNTS table has an alias, A, the hint must refer to the table by its alias, rather than by its name. Also, do not specify schema names in the hint, </quote>
>--brad skiles, dba
>Purdue University
>
>bwskiles_at_adpc.purdue.edu
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |