Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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?

matt

Former Member replied

Hi Matthew,

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.

bye

yk

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question