Re: Why my index is not used?

From: Ana Ribeiro <ana.ribeiro_at_reflective.com>
Date: 18 Jan 2007 01:10:18 -0800
Message-ID: <1169111417.418698.309570@38g2000cwa.googlegroups.com>

DA Morgan wrote:

> 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

Thanks for all your replies.
Daniel, why do you think that Oracle is assuming a higher cost if it chooses that index?
Unfortunately this table has 121K rows with runid=0 and only 10 rows with runid=1, as this is the development environment and the data is not real - in production exists hundreds of differents runids and each of them has approximately 10K rows.

Thanks again,
Ana
. Received on Thu Jan 18 2007 - 03:10:18 CST