Why my index is not used?

From: Ana Ribeiro <ana.ribeiro_at_reflective.com>
Date: 17 Jan 2007 04:54:22 -0800
Message-ID: <1169038461.970994.154100@v45g2000cwv.googlegroups.com>

Hi all,
I have the following query:

select rs.runid, rs.instanceid, rs.scriptid, sc.scriptname, rs.processnumber, rs.threadid,
min(st.steporder) as steporder, rs.cycleid, min(rs.startstamp) as startstamp,
sum(rs.measurement) as measurement, sum(rs.errorcount) as errorcount from step st, script sc, result rs
where

RS.RUNID=0 AND
rs.scriptid = sc.scriptid and
st.scriptid = rs.scriptid and
st.steporder = rs.steporder and
st.stepgroup <> '-1' and
st.stepgroup is not null and
sc.pluginid <> 2

group by
rs.runid, rs.instanceid, rs.processnumber, rs.scriptid, rs.threadid, rs.cycleid, st.stepgroup, sc.scriptname;

I have Statistics for my schema and the Optimizer_mode = ALL_ROWS. There are also 4 non-unique indexes in the RESULT table, which is the biggest one, with more than 100.000 rows:

INDEX_NAME COLUMN_NAME COLUMN_POSITION

---

INDX_RESULT_1	RUNID		1
INDX_RESULT_1	SCRIPTID	2
INDX_RESULT_1	STARTSTAMP	3
INDX_RESULT_1	ERRORCOUNT	4

INDX_RESULT_2	RUNID		1
INDX_RESULT_2	SCRIPTID	2
INDX_RESULT_2	STARTSTAMP	3

INDX_RESULT_3	RUNID		1
INDX_RESULT_3	SCRIPTID	2
INDX_RESULT_3	STARTSTAMP	3
INDX_RESULT_3	STEPORDER	4

IX_RESULTSTARTSTAMP	STARTSTAMP	1

This is the query plan:

PLAN_TABLE_OUTPUT

---

Plan hash value: 1866148270

---

| Id  | Operation            | Name   | Rows  | Bytes |TempSpc| Cost


(%CPU)| Time     |

---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        | 64701 |  4802K|       |  2397


(4)| 00:00:08 |

|   1 |  HASH GROUP BY       |        | 64701 |  4802K|    14M|  2397


(4)| 00:00:08 |

|*  2 |   HASH JOIN          |        |   109K|  8121K|       |   429


(10)| 00:00:02 |

|*  3 |    TABLE ACCESS FULL | STEP   |   742 |  8904 |       |     8


(0)| 00:00:01 |

|*  4 |    HASH JOIN         |        |   113K|  7122K|       |   415


(8)| 00:00:02 |

|*  5 |     TABLE ACCESS FULL| SCRIPT |     8 |   160 |       |     4


(0)| 00:00:01 |

|*  6 |     TABLE ACCESS FULL| RESULT |   121K|  5222K|       |   404

(7)| 00:00:02 |

---

Predicate Information (identified by operation id):

---

   2 - access("ST"."SCRIPTID"="RS"."SCRIPTID" AND
              "ST"."STEPORDER"="RS"."STEPORDER")
   3 - filter("ST"."STEPGROUP" IS NOT NULL AND "ST"."STEPGROUP"<>'-1')
   4 - access("RS"."SCRIPTID"="SC"."SCRIPTID")
   5 - filter("SC"."PLUGINID"<>2)
   6 - filter("RS"."RUNID"=0)

My question is: If in the query I set the RUNID value (RS.RUNID=0), why an index is not used?

Many thanks for the help!
Ana Received on Wed Jan 17 2007 - 06:54:22 CST