Oracle wan't to use index when using Select For All Entries In

Hello,

The problem occurs in system with Oracle 10.2.0.5.0. The problem is poor performance of accessing rows in table VEPO by field UNVEL but only when instruction FOR ALL ENTRIES IN is used.

The standard function module V51S_FIND_HU_ITEMS_DB is optimized regarding usage of index:

SELECT * FROM VEPO INTO TABLE LT_VEPO FOR ALL ENTRIES IN IT_VEKP
WHERE UNVEL = IT_VEKP-VENUM
"v_n_966691
%_hints oracle 'INDEX("&TABLE&" "VEPO______A" "VEPO~A" "VEPO^A")'
* %_hints oracle 'RULE("&TABLE&" "VEPO______A" "VEPO~A" "VEPO^A")'

"^_n_966691
MSSQLNT 'TABLE VEPO ABINDEX(A)' "n_1041589
MSSQLNT '&prefer_join 0&' "n_1373710
DB2 'USE VALUES FOR OPTIMIZATION'
DB2 '&SUBSTITUTE VALUES&' "SP_561801
DB6 'USE_OPTLEVEL 0'. "DB6: USE OPTLEVEL 0

ST05 shows that instruction "For All Entries In" is converted to query with operator IN:

321 VEPO PREPARE SELECT WHERE "MANDT" = :A0 AND "UNVEL" IN ( :A1 , :A2 , :A3 , :A4 , :A5 ) 5 VEPO OPEN SELECT WHERE "MANDT" = '100' AND "UNVEL" IN ( '0004137546' , '0004137548' , '0004137549' , '0004137563' , '0004137564' ) 3133099 (3 seconds!)

VEPO

FETCH 1403

and execution plan is like this:

SELECT

/*+

INDEX("VEPO" "VEPO______A" "VEPO~A" "VEPO^A")

*/

*

FROM

"VEPO"

WHERE

"MANDT" = :A0 AND "UNVEL" IN ( :A1 , :A2 , :A3 , :A4 , :A5 )

Execution Plan

SELECT STATEMENT ( Estimated Costs = 28 115 , Estimated #Rows = 4 000 867 )

2 TABLE ACCESS BY INDEX ROWID VEPO

( Estim. Costs = 28 115 , Estim. #Rows = 4 000 867 )

Estim. CPU-Costs = 1 040 852 180 Estim. IO-Costs = 27 847

1 INDEX RANGE SCAN VEPO~A

( Estim. Costs = 4 732 , Estim. #Rows = 4 000 867 )

Search Columns: 1

Estim. CPU-Costs = 195 365 509 Estim. IO-Costs = 4 681

Access Predicates Filter Predicates

As you can see there are Filter predicates:

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("MANDT"=:A0)

filter("UNVEL"=:A1 OR "UNVEL"=:A2 OR "UNVEL"=:A3 OR "UNVEL"=:A4 OR

"UNVEL"=:A5)

Because field UNVEL is not included in access predicates, the index is not used (or used but not in efficient way).

When the query is in form:

SELECT WHERE "MANDT" = '100' AND "UNVEL" = '1234567890'

both fields MANDT and UNVEL are in Access predicates and data are returned quickly.

Details of the index:

NONUNIQUE Index VEPO~A

Column Name #Distinct

MANDT 1 UNVEL 1

Last statistics date 24.09.2012 15:50 Analyze Method Sample 198 813 Rows Branch levels of B-Tree 2 Number of leaf blocks 23 405 Number of rows 4 050 680 Number of distinct keys 1 Average leaf blocks per key 21 495

Number of distinct keys is 1 because VEPO-UNVEL field is empty in the table (there is no hierarchy of handling units).

Statistics of the index are updated. The storage quality of index is 43%.

What is wrong with database?

I checked the same query in system of another client (also with ORACLE) and it is processed correctly.

At the moment we did modification of standard function module so it uses LOOP...ENDLOOP with SELECT statement inside instead of FOR ALL ENTRIES. Now it uses index but this is a temporary solution.

Access to other tables using FOR ALL ENTRIES IN is correct.

We have no more idea what is wrong.

Please help

Thank you for advance

Rafal