cost-based execution plan - incorporating cardinality?

Robert Koch gms_xy at gmx.de
Fri Oct 17 11:21:38 CEST 2014


SELECT
    opbel
FROM
    sapsr3.dfkkop
WHERE
    --mandt='200' and
    vkont='000802037649'
;

runs in 5 ms.


SELECT
    opbel
FROM
    sapsr3.dfkkop
WHERE
    mandt='200' and
    vkont='000802037649'
;

runs in 15 s. (Factor 3.000) Column "mandt" has a cardinality of 2/1.000.000.000 (2 distinct values at 1 billion rows). Column vkont has a cardinality of 12.960.000/1.000.000.000. So I would expect to do the lookup on column vkont.

Output of EXPLAIN:

EXPLAIN
SELECT
    opbel
FROM
    sapsr3.dfkkop
WHERE
    --mandt='200' and
    vkont='000802037649'
;

mal                                                                                                                                              
-----------------------------------------------------------------------------------------------------------------------------------------------  
function user.s11_3{autoCommit=true}(A0:str):void;                                                                                               
X_3 := sql.mvc();                                                                                                                                
X_7 := sql.bind(X_3,"sapsr3","dfkkop","vkont",0);                                                                                                
X_4:bat[:oid,:oid]  := sql.tid(X_3,"sapsr3","dfkkop");                                                                                           
X_45 := algebra.subselect(X_7,X_4,A0,A0,true,true,false);                                                                                        
(X_10,r1_10) := sql.bind(X_3,"sapsr3","dfkkop","vkont",2);                                                                                       
X_46 := algebra.subselect(r1_10,A0,A0,true,true,false);                                                                                          
X_13 := sql.bind(X_3,"sapsr3","dfkkop","vkont",1);                                                                                               
X_47 := algebra.subselect(X_13,X_4,A0,A0,true,true,false);                                                                                       
X_15 := sql.subdelta(X_45,X_4,X_10,X_46,X_47);                                                                                                   
X_17 := sql.bind(X_3,"sapsr3","dfkkop","opbel",0);                                                                                               
(X_19,r1_22) := sql.bind(X_3,"sapsr3","dfkkop","opbel",2);                                                                                       
X_21 := sql.bind(X_3,"sapsr3","dfkkop","opbel",1);                                                                                               
X_22 := sql.projectdelta(X_15,X_17,X_19,r1_22,X_21);                                                                                             
X_23 := sql.resultSet(1,1,X_22);                                                                                                                 
sql.rsColumn(X_23,"sapsr3.dfkkop","opbel","varchar",12,0,X_22);                                                                                  
X_28 := io.stdout();                                                                                                                             
sql.exportResult(X_28,X_23);                                                                                                                     
end s11_3;                                                                                                                                       
# querylog.define("select\n    opbel\nfrom\n    sapsr3.dfkkop\nwhere\n    --mandt=\\'200\\' and\n    vkont=\\'000802037649\\';","default_pipe")  
# optimizer.mitosis()                                                                                                                            
# optimizer.dataflow()                                                                                                                           



EXPLAIN
SELECT
    opbel
FROM
    sapsr3.dfkkop
WHERE
    mandt='200' and
    vkont='000802037649'
;



mal                                                                                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------  
function user.s12_3{autoCommit=true}(A0:str,A1:str):void;                                                                                               
X_4 := sql.mvc();                                                                                                                                       
X_8 := sql.bind(X_4,"sapsr3","dfkkop","vkont",0);                                                                                                       
X_16 := sql.bind(X_4,"sapsr3","dfkkop","mandt",0);                                                                                                      
X_5:bat[:oid,:oid]  := sql.tid(X_4,"sapsr3","dfkkop");                                                                                                  
X_54 := algebra.subselect(X_16,X_5,A0,A0,true,true,false);                                                                                              
(X_18,r1_21) := sql.bind(X_4,"sapsr3","dfkkop","mandt",2);                                                                                              
X_55 := algebra.subselect(r1_21,A0,A0,true,true,false);                                                                                                 
X_20 := sql.bind(X_4,"sapsr3","dfkkop","mandt",1);                                                                                                      
X_56 := algebra.subselect(X_20,X_5,A0,A0,true,true,false);                                                                                              
X_21 := sql.subdelta(X_54,X_5,X_18,X_55,X_56);                                                                                                          
X_57 := algebra.subselect(X_8,X_21,A1,A1,true,true,false);                                                                                              
(X_11,r1_11) := sql.bind(X_4,"sapsr3","dfkkop","vkont",2);                                                                                              
X_58 := algebra.subselect(r1_11,A1,A1,true,true,false);                                                                                                 
X_14 := sql.bind(X_4,"sapsr3","dfkkop","vkont",1);                                                                                                      
X_59 := algebra.subselect(X_14,X_21,A1,A1,true,true,false);                                                                                             
X_23 := sql.subdelta(X_57,X_21,X_11,X_58,X_59);                                                                                                         
X_24 := sql.bind(X_4,"sapsr3","dfkkop","opbel",0);                                                                                                      
(X_26,r1_32) := sql.bind(X_4,"sapsr3","dfkkop","opbel",2);                                                                                              
X_28 := sql.bind(X_4,"sapsr3","dfkkop","opbel",1);                                                                                                      
X_29 := sql.projectdelta(X_23,X_24,X_26,r1_32,X_28);                                                                                                    
X_30 := sql.resultSet(1,1,X_29);                                                                                                                        
sql.rsColumn(X_30,"sapsr3.dfkkop","opbel","varchar",12,0,X_29);                                                                                         
X_36 := io.stdout();                                                                                                                                    
sql.exportResult(X_36,X_30);                                                                                                                            
end s12_3;                                                                                                                                              
# querylog.define("explain\nselect\n    opbel\nfrom\n    sapsr3.dfkkop\nwhere\n    mandt=\\'200\\' and\n    vkont=\\'000802037649\\';","default_pipe")  
# optimizer.mitosis()                                                                                                                                   
# optimizer.dataflow()                                                                                                                                  






More information about the developers-list mailing list