Re: Why my index is not used?

On Jan 18, 10:10 am, "Ana Ribeiro" <ana.ribe..._at_reflective.com> 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
> > damor..._at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Group
> >www.psoug.orgThanks 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
> .- Hide quoted text -- Show quoted text -

Just a warning.
One of the most frequently made errors in development using CBO is working with non-representative data.
Then the application goes live on a completely different dataset and the fireworks begin...
Seems you have some work to do to have the development data replaced.

-- 
Sybrand Bakker
Senior Oracle DBA