Re: Why my index is not used?

Ana Ribeiro wrote:
> 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

Use a HINT to force index usage and look at the resulting cost.

For some reason, it appears, Oracle is assuming a higher cost if it chooses that index. There are a number of possible reasons. Some mentioned by Sybrand but what catches my eye is that it appears that there are 121K rows with runid=0. What percentage of the rows in the table is that?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org