Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Hint Help!

Re: Hint Help!

From: Edward Nunez <enunez_at_hotmail.com>
Date: 1997/05/23
Message-ID: <3385E707.87F1A11@hotmail.com>#1/1

Brad Skiles 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

To me everything seems ok, except that you need to make reference to the table alias,

So change:
  SELECT /*+ INDEX(TRANSACTION TRANSACT_DEPT) */ to:
SELECT /*+ INDEX(AL9 TRANSACT_DEPT) */ hope this helps, please let me know about it

Edward Nunez
Syntel, Inc.
Oracle Consultant
enunez_at_hotmail.com
Office: (319) 375-1160
Home: (319) 396-7039 Received on Fri May 23 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US