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

"Radoulov, Dimitre" <cichomitiko_at_gmail.com> wrote in message news:glsvh5$imh$1_at_reader.motzarella.org...
> -------------------------------------------------------------------------------------------------
> | 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)
>

I'm assuming this is the SQL with the double predicates - 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).

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html