SQL trace and "OR" in the where clause

[matt]

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]

Havent got that table here... so theorising

Perhaps since the statement only contains EQ operators the db parser can create two specific statements?

Sometimes I wish I was more than ABAP monkey

[christine_evans]

Using OR in a WHERE clause does not negate the use of indexes. In fact, you probably use them all the time without realising. For example, any statement that uses FOR ALL ENTRIES will create a real SQL statement containing multiple OR statements in the WHERE clause, as will using a list of values in a RANGES or SELECT-OPTIONS table (check it in ST05). Ok, the latter might generate an IN (field1, field2) statement instead, but an IN clause to all intents and purposes is the same as a set of OR clauses.

To my knowledge - and my knowledge of Oracle dates back to pre release 7 - this has always been the case.

[matt]

So, changing to *IN (val1, val2) * is unlikely to have any effect?

thanks

matt

[christine_evans]

I shouldn't think it would make any difference. When you say something like field1 IN ( 'val1', 'val2' ), as far as the database is concerned this must be treated in the same way as field1 = 'val1' OR field1 = 'val2', since it is effectively the same statement put in a different way. If an index has been defined on field1, both statements should use it. But always check in ST05......

[Former Member]

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

[matt]

Last time I was involved in performance tuning at this level was before oracle 8! I still plan to remain unevolved, however.

Thanks

matt