index skip scan for 2 value in where clause for second column

Question

I create an index on (SWcode,cdcardno)

and I run this query :

SELECT cd.*
  FROM KS cd
  where cd.cdcardno in ('6219862012953805')
 ORDER BY cd.ROWID

and oracle use index skip scan and it is true . but when I run this query :

SELECT cd.*
  FROM KS cd
  where cd.cdcardno in ('6219861009150391','6219862012953805')
 ORDER BY cd.ROWID

oracle use Full scan. I cant understand why.

Answer 1

Sometimes Oracle flaps between two exec plans, when one of them is bad and the other one is even worse. Create an index where cdcardno is on 1st position in the list. Or just flip order of columns in the current index.

Answer 2

The optimizer will generate the execution plan based on cost. That in turn is based on statistics. The answers you will receive will be guesses unless you provide some diagnostic data, such as a SQL Monitor report.