Skip to Content
0
Apr 30, 2010 at 10:38 AM

Skip scan used instead of (better?) range scan

376 Views

Hello forum,

I have this simple query (this is oracle 10g):

SELECT                       
  *                          
FROM                         
  zfieligiblercvbl           
WHERE                        
  mandt = :a0 and bukrs = :a1

There are (among other) the following indexes:

UNIQUE     Index   ZFIELIGIBLERCVBL~0           
                                                
MANDT                                          1
BUKRS                                          8
BELNR                                    106.478
GJAHR                                          7
BUZEI                                          4

NONUNIQUE  Index   ZFIELIGIBLERCVBLZ2           
                                                
Column Name                     #Distinct                                                       
DAT_OFFER                                    390
BUKRS                                          8
FLG_OFFER                                      2

I expect that the primary key would be used, but strangely index Z2 is used:

SELECT STATEMENT ( Estimated Costs = 519 , Estimated #Rows = 25.841 )                                                                                
5  2 TABLE ACCESS BY INDEX ROWID ZFIELIGIBLERCVBL                      
         ( Estim. Costs = 518 , Estim. #Rows = 25.841 )                    
         Estim. CPU-Costs = 7.715.825 Estim. IO-Costs = 517                                                                                
1 INDEX SKIP SCAN ZFIELIGIBLERCVBLZ2                            
             ( Estim. Costs = 79 , Estim. #Rows = 25.841 )                 
             Search Columns: 1                                             
             Estim. CPU-Costs = 1.591.961 Estim. IO-Costs = 78

(this is too getting long; more details will come below)