Re: Why my index is not used?

On Jan 17, 1:54 pm, "Ana Ribeiro" <ana.ribe..._at_reflective.com> 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

Remarks
 Please always post your exact version
Questions:
did you change optimizer_index_cost_adj and/or optimizer_index_caching from their disastrous defaults and/or did you gather system stats? Do you have accurate histograms on those tables. How many records in result have runid 0?

Further remarks
all_rows favors full table scans and hash joins. Try first_rows. Try gathering system stats or change the opt* parameters to something sensible (90 for index caching and 40 for cost_adj) Step is the driving table. There are no predicates which can use an index op Step.
Likewise for script.
Consequently it isn't strange the optimizer goes for a full table scan on results too, especially as the leading column of almost all indices (indices need to be revised too) doesn't participate in the join.

Hth

-- 
Sybrand Bakker
Senior Oracle DBA