SQL trace and "OR" in the where clause
My understanding is that if we use OR in the where clause, then this always ends up with a sequential table scan.
This is the SQL:
SELECT * FROM rszeltxref INTO TABLE e_ts_eltxref WHERE objvers = i_objvers AND ( infocube = i_by_infocube OR infocube = rsz_c_infocube_nothing ).
This is the trace:
SELECT * FROM "RSZELTXREF" WHERE "OBJVERS" = :A0 AND ( "INFOCUBE" = :A1 OR "INFOCUBE" = :A2 )& Execution Plan SELECT STATEMENT ( Estimated Costs = 159 , Estimated #Rows = 0 ) 3 INLIST ITERATOR 2 TABLE ACCESS BY INDEX ROWID RSZELTXREF ( Estim. Costs = 159 , Estim. #Rows = 1.253 ) Estim. CPU-Costs = 3.844.896 Estim. IO-Costs = 159 Estim. Bytes: 92.722 1 INDEX RANGE SCAN RSZELTXREF~IC ( Estim. Costs = 26 , Estim. #Rows = 5.011 ) Estim. CPU-Costs = 1.198.800 Estim. IO-Costs = 26 Search Columns: 1
Is my understanding correct? Or has db technology moved on since I last considered these things, and sap/oracle handles an OR quite effectively?
Bernd Boecker replied
we all evolve in some way - no difference to databases.
OR and IN list a merely the same for the optimizer - they have the same access paths.
The IN list is expanded into OR's.
You see the INLIST ITERATOR path in your St05. This came with Oracle 8 and higher.
Before indeed they used the CONCATENATION path wich resulted
a) in a full table scan
b) or by using in index with a concatenation of subqueries each taken a value from the list
imagine this "ancient" access paths with a long IN list or OR's...
if a new version of ORACLE is supported by SAP I always check changes on access paths -
so you can in ST05.