Oracle Database Discussions

Dears,

I would like to expose you a situation that happens in Production. I hope I will be clear in explaining it (data base 10gR2)

A query was performing very bad in production. This query was involving a range partitioned table on a date column say rang_part_tab for example that could be described as follows
ord_num
part_id
part_dat
col_num1
col_num2
col_dat1
col_dat2
col_varch1


In this query there was a predicate on this particular rang_part_tab table as follow

where col_num1 = :num1
and col_dat1 between :date_v1 and :date_v2
and col_dat2 between :date_v1 and :date_v2
and part_dat = other_table.part_dat ==> join on the partition key
etc...

In this table there were two indexes (among others) defined on
indx_1 (col_num1) global index
indx_2 (part_id, part_dat) locally partitioned index


I decided to enrich the b-tree indx_1 to be indx_1(col_num1, part_dat , col_dat1, col_dat2)

and the query starts performing very well accessing my partitioned table
5 4 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'RANG_PART_TAB' (TABLE) (Cost=1159 Card=1 Bytes=54)
6 5 INDEX (RANGE SCAN) OF 'INDX_1' (INDEX) (Cost=9 Card=115)

The query kept runing very fast from 19/11 up to 1/12/2009. On 2/12/2009 it starts again performing very badly. The xplain plan has changed as follows

* 6 | TABLE ACCESS BY LOCAL INDEX ROWID| RANG_PART_TAB | 1 | 1 | 1304 |00:00:46.60 | 644K| 17741 | |
* 7 | INDEX SKIP SCAN | INDX_2 | 1 | 36550 | 810K|00:12:35.14 | 9263 | 7671 | |

It has suddenly let down the use of indx_1 and start using the other locally partitioned index INDX_2 using a skip scan

And suddenly again, today 3/12/2009, it starts performing well and starts again using the index INDX_2

What could be the reason of this go-and-back in using the index INDX_1. I don't want to hint the query to use the index INDX_1. I would like instead to understand the reason of this random use of index

Thanks a lot

Mohamed Houri