Re: Hint Help!

Code your hint as

SELECT /*+ INDEX(AL9 TRANSACT_DEPT) */ If you use an alias in your SQL, then you must use the alias name, instead of the table name in the hint.

Stephen Miller
ISM, Montreal

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
>WHERE
> AL9.FISC_YR='1996-97' AND
> AL9.DEPT IN ('1330','1333','1335','1336','1337','1338') AND
> AL9.OBJ BETWEEN '52000' AND '52999';
>
><<Returned rows omitted>>
>
>12941 rows selected.
>
> real: 3617061
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Cost=2424 Optimizer=CHOOSE
> 1 0 TABLE ACCESS (BY ROWID) OF 'TRANSACTION'
> 2 1 INDEX (RANGE SCAN) OF 'TRANSACT_OBJ'
>
>
>Statistics
>----------------------------------------------------------
> 8 recursive calls
> 7 db block gets
> 2682958 consistent gets
> 473165 physical reads
> 698 redo size
> 487591 bytes sent via SQL*Net to client
> 18528 bytes received via SQL*Net from client
> 2591 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 12941 rows processed
>
>For comparitive purposes, and to assure you that there is a
>TRANSACT_DEPT index, here is the same query information when I apply the
>rule hint:
>
>SELECT /*+ RULE */
> AL9.FUND,
> AL9.PROJ,
> AL9.OBJ,
> AL9.AMOUNT,
> AL9.DEPT
>FROM
> COMPLETE.TRANSACTION AL9
>WHERE
> AL9.FISC_YR='1996-97' AND
> AL9.DEPT IN ('1330','1333','1335','1336','1337','1338') AND
> AL9.OBJ BETWEEN '52000' AND '52999';
>
>12941 rows selected.
>
> real: 80666
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=HINT: RULE
> 1 0 CONCATENATION
> 2 1 TABLE ACCESS (BY ROWID) OF 'TRANSACTION'
> 3 2 AND-EQUAL
> 4 3 INDEX (RANGE SCAN) OF 'TRANSACT_DEPT'
> 5 3 INDEX (RANGE SCAN) OF 'TRANSACT_FISC_YR'
> 6 1 TABLE ACCESS (BY ROWID) OF 'TRANSACTION'
> 7 6 AND-EQUAL
> 8 7 INDEX (RANGE SCAN) OF 'TRANSACT_DEPT'
> 9 7 INDEX (RANGE SCAN) OF 'TRANSACT_FISC_YR'
> 10 1 TABLE ACCESS (BY ROWID) OF 'TRANSACTION'
> 11 10 AND-EQUAL
> 12 11 INDEX (RANGE SCAN) OF 'TRANSACT_DEPT'
> 13 11 INDEX (RANGE SCAN) OF 'TRANSACT_FISC_YR'
> 14 1 TABLE ACCESS (BY ROWID) OF 'TRANSACTION'
> 15 14 AND-EQUAL
> 16 15 INDEX (RANGE SCAN) OF 'TRANSACT_DEPT'
> 17 15 INDEX (RANGE SCAN) OF 'TRANSACT_FISC_YR'
> 18 1 TABLE ACCESS (BY ROWID) OF 'TRANSACTION'
> 19 18 AND-EQUAL
> 20 19 INDEX (RANGE SCAN) OF 'TRANSACT_DEPT'
> 21 19 INDEX (RANGE SCAN) OF 'TRANSACT_FISC_YR'
> 22 1 TABLE ACCESS (BY ROWID) OF 'TRANSACTION'
> 23 22 AND-EQUAL
> 24 23 INDEX (RANGE SCAN) OF 'TRANSACT_DEPT'
> 25 23 INDEX (RANGE SCAN) OF 'TRANSACT_FISC_YR'
>
>
>Statistics
>----------------------------------------------------------
> 8 recursive calls
> 7 db block gets
> 257293 consistent gets
> 5356 physical reads
> 680 redo size
> 487591 bytes sent via SQL*Net to client
> 18539 bytes received via SQL*Net from client
> 2591 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 12941 rows processed
>
>Any help is greatly appreciated!
>
>--brad skiles, dba
>Purdue University
>
>bwskiles_at_adpc.purdue.edu
Received on Sun May 25 1997 - 00:00:00 CDT