ABAP中FOR ALL ENTRIES语句深入解析

FOR ALL ENTRIES 用内表做限制条件取数据库中的数据，该语法在生成Native SQL时会根据内表数据将OPEN SQL拆分成多条在数据库中执行，在内表数据量较大的时候会发生性能问题。
除了在应用层批次拆分内表外（ABAP内表数据分批次处理程序的代码模板），还可以使用HINT语句来控制。

性能调优项目中常见下面这种sql：

1. SELECT * FROM [..] for all entries in [..] where [..]
   
2. %_HINTS ORACLE '&prefer_in_itab_opt 1&&prefer_union_all 1&&prefer_fix_blocking -1&'.

复制代码

这是针对ORACLE数据库的取数性能调优，其中prefer_i开头的这些参数，是可以通过配置参数文件可以调整的。

RZ1--转到--参数文件值--服务器的-->双击服务器名称

1、rsdb/prefer_join
使用连接(JOIN)的方式实现FOR ALL ENTRIES，0禁用 1启用

2、rsdb/prefer_union_all
= 0 时，条件之间用OR连接

1. SELECT ... WHERE f = itab[1]-f
   
2. OR f = itab[2]-f
   
3. ...
   
4. OR f = itab[N]-f

复制代码

= 1时，条件之间用UNION ALL连接

1. SELECT ... WHERE f = itab[1]-f
   
2. UNION ALL SELECT...WHERE f = itab[2]-f
   
3. ....
   
4. UNION ALL SELECT...WHERE f = itab[N]-f

复制代码

case 1：

1. SELECT * UP TO 30 ROWS
   
2. INTO TABLE lt_bkpf
   
3. FROM bkpf.
   
4. 
   
5. SELECT *
   
6. INTO TABLE lt_bseg
   
7. FROM bsas
   
8. FOR ALL ENTRIES IN lt_bkpf
   
9. WHERE bukrs = lt_bkpf-bukrs
   
10.   AND belnr = lt_bkpf-belnr
    
11.   AND gjahr = lt_bkpf-gjahr
    
12.   %_HINTS ORACLE '&prefer_union_all 1&&max_blocking_factor 7&'.

复制代码

ST05跟踪结果：

1. SELECT * FROM "BSAS" WHERE "MANDT" = :A0 AND "BUKRS" = :A1 AND "BELNR" = :A2 AND "GJAHR" = :A3
   
2. UNION ALL
   
3. SELECT * FROM "BSAS" WHERE "MANDT" = :A4 AND "BUKRS" = :A5 AND "BELNR" = :A6 AND "GJAHR" = :A7
   
4. UNION ALL
   
5. SELECT * FROM "BSAS" WHERE "MANDT" = :A8 AND "BUKRS" = :A9 AND "BELNR" = :A10 AND "GJAHR" = :A11
   
6. UNION ALL
   
7. SELECT * FROM "BSAS" WHERE "MANDT" = :A12 AND "BUKRS" = :A13 AND "BELNR" = :A14 AND "GJAHR" = :A15
   
8. UNION ALL
   
9. SELECT * FROM "BSAS" WHERE "MANDT" = :A16 AND "BUKRS" = :A17 AND "BELNR" = :A18 AND "GJAHR" = :A19
   
10. UNION ALL
    
11. SELECT * FROM "BSAS" WHERE "MANDT" = :A20 AND "BUKRS" = :A21 AND "BELNR" = :A22 AND "GJAHR" = :A23
    
12. UNION ALL
    
13. SELECT * FROM "BSAS" WHERE "MANDT" = :A24 AND "BUKRS" = :A25 AND "BELNR" = :A26 AND "GJAHR" = :A27

复制代码

3、rsdb/prefer_in_itab_opt
该参数被设置为1，如果where条件中仅有一个字段被内表限制，则将内表的字段放到IN条件中
SQL IN条件比OR效率高，启用该参数可以提高效率

1. SELECT ... FOR ALL ENTRIES IN itab WHERE f = itab-f.

复制代码

1. SELECT ... WHERE f IN (itab[1]-f, itab[2]-f, ..., itab[N]-f)

复制代码

Case 2：启用prefer_in_itab_opt

1. DATA:lt_bkpf TYPE TABLE OF bkpf,
   
2.      lt_bseg TYPE TABLE OF bsas.
   
3. DATA:lv_bkpf TYPE i,
   
4.      lv_bseg TYPE i.
   
5. 
   
6. SELECT * UP TO 30 ROWS
   
7. INTO TABLE lt_bkpf
   
8. FROM bkpf.
   
9. 
   
10. SELECT *
    
11. INTO TABLE lt_bseg
    
12. FROM bsas
    
13. FOR ALL ENTRIES IN lt_bkpf
    
14. WHERE belnr = lt_bkpf-belnr
    
15. %_HINTS ORACLE '&prefer_in_itab_opt 1&&max_blocking_factor 11&'.

复制代码

ST05跟踪结果：

1. SELECT * FROM "BSAS"
   
2. WHERE "MANDT" = :A0 AND "BELNR" IN ( :A1 , :A2 , :A3 , :A4 , :A5 )

复制代码

Case 3：禁用prefer_in_itab_opt

1. DATA: lt_bkpf TYPE TABLE OF bkpf,
   
2.       lt_bseg TYPE TABLE OF bsas.
   
3. DATA: lv_bkpf TYPE i,
   
4.       lv_bseg TYPE i.
   
5. 
   
6. SELECT * UP TO 30 ROWS
   
7. INTO TABLE lt_bkpf
   
8. FROM bkpf.
   
9. 
   
10. SELECT *
    
11. INTO TABLE lt_bseg
    
12. FROM bsas
    
13. FOR ALL ENTRIES IN lt_bkpf
    
14. WHERE belnr = lt_bkpf-belnr
    
15. %_HINTS ORACLE '&prefer_in_itab_opt 0&&max_blocking_factor 11&'.

复制代码

ST05跟踪结果：

1. SELECT * FROM "BSAS"
   
2. WHERE "MANDT" = :A0 AND "BELNR" = :A1 OR
   
3. "MANDT" = :A2 AND "BELNR" = :A3 OR
   
4. "MANDT" = :A4 AND "BELNR" = :A5 OR
   
5. "MANDT" = :A6 AND "BELNR" = :A7 OR
   
6. "MANDT" = :A8 AND "BELNR" = :A9 OR
   
7. "MANDT" = :A10 AND "BELNR" = :A11 OR
   
8. "MANDT" = :A12 AND "BELNR" = :A13 OR
   
9. "MANDT" = :A14 AND "BELNR" = :A15 OR
   
10. "MANDT" = :A16 AND "BELNR" = :A17 OR
    
11. "MANDT" = :A18 AND "BELNR" = :A19 OR
    
12. "MANDT" = :A20 AND "BELNR" = :A21

复制代码

4、rsdb/max_blocking_factor
该参数设定每个SQL Statement处理的内表条件的个数。
如果内表数据为50条，rsdb/max_blocking_factor = 5，则实际生成10条SQL Statement，每个SQL Statement包含5组条件
Case 4：

1. DATA: lt_bkpf TYPE TABLE OF bkpf,
   
2.       lt_bseg TYPE TABLE OF bsas.
   
3. DATA: lv_bkpf TYPE i,
   
4.       lv_bseg TYPE i.
   
5. 
   
6. SELECT * UP TO 30 ROWS
   
7. INTO TABLE lt_bkpf
   
8. FROM bkpf.
   
9.   
   
10. SELECT *
    
11. INTO TABLE lt_bseg
    
12. FROM bsas
    
13. FOR ALL ENTRIES IN lt_bkpf
    
14. WHERE bukrs = lt_bkpf-bukrs
    
15.   AND belnr = lt_bkpf-belnr
    
16.   AND gjahr = lt_bkpf-gjahr
    
17.   %_HINTS ORACLE '&max_blocking_factor 7&'.

复制代码

ST05跟踪结果：

1. SELECT * FROM "BSAS"
   
2. WHERE
   
3. "MANDT" = :A0 AND "BUKRS" = :A1 AND "BELNR" = :A2 AND "GJAHR" = :A3 OR
   
4. "MANDT" = :A4 AND "BUKRS" = :A5 AND "BELNR" = :A6 AND "GJAHR" = :A7 OR
   
5. "MANDT" = :A8 AND "BUKRS" = :A9 AND "BELNR" = :A10 AND "GJAHR" = :A11 OR
   
6. "MANDT" = :A12 AND "BUKRS" = :A13 AND "BELNR" = :A14 AND "GJAHR" = :A15 OR
   
7. "MANDT" = :A16 AND "BUKRS" = :A17 AND "BELNR" = :A18 AND "GJAHR" = :A19 OR
   
8. "MANDT" = :A20 AND "BUKRS" = :A21 AND "BELNR" = :A22 AND "GJAHR" = :A23 OR
   
9. "MANDT" = :A24 AND "BUKRS" = :A25 AND "BELNR" = :A26 AND "GJAHR" = :A27

复制代码

5、rsdb/max_in_blocking_factor
同rsdb/max_blocking_factor类似，该参数针对rsdb/prefer_in_itab_opt，即WHERE 条件为IN的情况。

6、rsdb/min_blocking_factor
rsdb/prefer_fix_blocking = 1 时，该参数生效。

7、rsdb/min_in_blocking_factor
同rsdb/min_blocking_factor类似，该参数针对rsdb/prefer_in_itab_opt，即WHERE 条件为IN的情况。

8、rsdb/prefer_fix_blocking
该参数配合rsdb/min_blocking_factor使用，内表中数据的条数不能被 rsdb/max_blocking_factor 整除时，剩余数据是否均匀的生成SQL Statment由该参数控制，假设内表数据为20条，rsdb/max_blocking_factor=11，rsdb/min_blocking_factor=5 ，当rsdb/prefer_fix_blocking = 0 ，会生成两条SQL Statement，第一条条件为10个，第二条为9个.当rsdb/prefer_fix_blocking = 1时，会生成3条SQL语句，第一条10个条件，第二条和第三条5个条件。
Oracle在解析SQL时可以使用替换变量,相同的SQL在执行时，不需要重新解析,可以提高性能，设置该参数可以保证SQL Statement相同。

9、Note
v rsdb/prefer_fix_blocking和rsdb/min_in_blocking_factor针对少量数据，合理设置可有效利用Oracle的替代变量，在使用HINT优化大量数据时指定这连个参数意义不大vrsdb/max_blocking_factor 设置的过大可能会使SQL超长，造成程序DUMP，故参数文件中应谨慎设置，在HINT中rsdb/max_blocking_factor可以设置的大些，但要注意需测试出合理的值，提高性能的同时避免程序DUMP，实际使用中 ECC 5 SQL Statement中最多包含320组条件，即使max_blocking_factor设置为1000，实际仍按320处理簇表无法利用max_blocking_factor参数提高性能，在ECC5测试，取BSEG每次仅处理一条数据。

SAP NOTE 48230

10、HINTS扩展

• 指定使用全表扫描：%_HINTS ORACLE 'FULL(table_name)'
  表示扫描整个表
• 指定索引：%_HINTS ORACLE 'INDEX("table_name" " index_name") '
  表示扫描索引表
  

感谢分享

了解学习一下