Hint Help!

From: Brad Skiles <bwskiles_at_adpc.purdue.edu>
Date: 1997/05/23
Message-ID: <3385B38D.75B8@adpc.purdue.edu>#1/1

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 Fri May 23 1997 - 00:00:00 CDT