Re: Why my index is not used?

From: Ana Ribeiro <ana.ribeiro_at_reflective.com>
Date: 19 Jan 2007 01:13:41 -0800
Message-ID: <1169198021.454184.273210@v45g2000cwv.googlegroups.com>

DA Morgan wrote:

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

>

> I assume it because it is the reason the optimizer makes choices. The
> optimizer selects the plan it believes will have the lowest cost.

>

> In your case, with the vast majority of the rows having RUNID=0, the
> index is a waste of resources except when querying for a RUNID other
> than zero. And if that were to be the case in production then I would
> recommend a function based index that converts RUNID to NULL if it is 0.
> Thus only indexing non-zero values.
> --
> 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

Many thanks!!!
Ana Received on Fri Jan 19 2007 - 03:13:41 CST