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
Received on Thu Jan 18 2007 - 12:36:06 CST
