二维码

[基础语法] ABAP中FOR ALL ENTRIES语句深入解析

Twilight发表于 2016-12-16 13:51Twilight 最后回复于 2016-12-16 13:51 [复制链接] 1044 0

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--转到--参数文件值--服务器的-->双击服务器名称
RZ10.jpg
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. SELECT *
  5. INTO TABLE lt_bseg
  6. FROM bsas
  7. FOR ALL ENTRIES IN lt_bkpf
  8. WHERE bukrs = lt_bkpf-bukrs
  9.   AND belnr = lt_bkpf-belnr
  10.   AND gjahr = lt_bkpf-gjahr
  11.   %_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. SELECT * UP TO 30 ROWS
  6. INTO TABLE lt_bkpf
  7. FROM bkpf.

  8. SELECT *
  9. INTO TABLE lt_bseg
  10. FROM bsas
  11. FOR ALL ENTRIES IN lt_bkpf
  12. WHERE belnr = lt_bkpf-belnr
  13. %_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. SELECT * UP TO 30 ROWS
  6. INTO TABLE lt_bkpf
  7. FROM bkpf.

  8. SELECT *
  9. INTO TABLE lt_bseg
  10. FROM bsas
  11. FOR ALL ENTRIES IN lt_bkpf
  12. WHERE belnr = lt_bkpf-belnr
  13. %_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. SELECT * UP TO 30 ROWS
  6. INTO TABLE lt_bkpf
  7. FROM bkpf.
  8.   
  9. SELECT *
  10. INTO TABLE lt_bseg
  11. FROM bsas
  12. FOR ALL ENTRIES IN lt_bkpf
  13. WHERE bukrs = lt_bkpf-bukrs
  14.   AND belnr = lt_bkpf-belnr
  15.   AND gjahr = lt_bkpf-gjahr
  16.   %_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") '
    表示扫描索引表
回复

使用道具 举报

快速回帖

本版积分规则
您需要登录后才可以回帖 登录 | 注册有礼

快速回复 返回顶部 返回列表