0

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.

Mahsa ehsani
  • 117
  • 12
  • Skip scan can be chosen by oracle when you have two or more fields in your index, and oracle chooses to "skip" the first or more fields and "scan" the index from a field lower down the index tree. It is true what they say about histograms and data distribution, but it does not make sense to expect a skip scan if you choose only one field in your WHERE clause. – sandman Feb 10 '17 at 13:54

2 Answers2

0

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.

ibre5041
  • 4,903
  • 1
  • 20
  • 35
  • but why this happen? I read , for example if we have 4 different values for first column ,in first query , oracle split table to 4 sub table and search in each of them with index on second column. is it true? if yes , why when i enter two value for second column , oracle does not do some thing like that? – Mahsa ehsani Jan 03 '17 at 08:02
  • The possible explanation is histograms. Oracle estimates, that the latter query will return more rows. Anyway `SKIP SCAN` is one of the "worst" possible index usages. Your real problem is, that you do not have an index, where `cdcardno` is the leading column. – ibre5041 Jan 03 '17 at 08:46
0

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.

BobC
  • 4,208
  • 1
  • 12
  • 15