Re: Yet another "why is my index not used" question

>> -------------------------------------------------------------------------------------------------
>> | Id  | Operation                         |  Name       | Rows  | Bytes | 
>> Cost  | Pstart| Pstop |
>> -------------------------------------------------------------------------------------------------
>> |   0 | SELECT STATEMENT                  |             | 55964 | 9072K| 
>> 588 |       |       |
>> |   1 |  TABLE ACCESS BY LOCAL INDEX ROWID| T           | 55964 | 9072K| 
>> 588 |    18 |    18 |
>> |*  2 |   INDEX RANGE SCAN                | IDX_2       |  1142 | |   357 
>> |    18 |    18 |
>> -------------------------------------------------------------------------------------------------
>>
>> Predicate Information (identified by operation id):
>> ---------------------------------------------------
>>
>>    2 - access("T"."VMENMAG"=18 AND "T"."VMEMOIS"=200810)
>>        filter("T"."VMENMAG"=18 AND "T"."VMEMOIS"=200810)
>>

Right,
sorry for not having posted the statement.

> - this looks
> like a bug, and when I tested your scripts against 9.2.0.6 I didn't
> get the same predicate information on the index.
>
> Note - the filter VNENMAG=18 should not be there. It's the first column
> of the multi-column index, and it's unique within the partition (and known
> to
> be unique by virtue of the list definition). The filter on vnemois has to
> be
> there as it is the third column of the index and you don't reference the
> second.
>
> In my 9.2.0.6, I don't get the redundant vnenmag filter.
>
> Notice too that the index Rows figure is 1142, but the table Rows
> figure is 55,964 - the factor of 50 difference is probably the effect
> of double counting the vnenmag predicate. As a side effect, this
> has also reduced the cost of visiting the table (11,000+ down to 558),
> although the cardinality of the table visit has not changed because
> of the redundant predicate.
>
>
> In passing, you could compress this index on the first column since
> it's always the same value. It's possible that compression on more
> that just the first column would be beneficial (try validating one
> partition at a time when you don't mind locking the table and see
> what the opt_compr_count is in index_stats).
>
> If you want this index to be used more effectively (and automatically)
> for this query then you could consider the possibility of swapping the
> second and third columns (so that the query predicates match the
> first two columns of the index).

Thank you for your suggestions,
I'll try to implement them.

Best regards
Dimitre Received on Thu Jan 29 2009 - 14:34:40 CST