FOR ALL ENTRIES 优化

货铺QQ群号：834508274

微信群不能扫码进了，可以加我微信SAPliumeng拉进群，申请时请提供您哪个模块顾问，否则是一律不通过的。

进群统一修改群名片，例如BJ_ABAP_森林木。群内禁止发广告及其他一切无关链接，小程序等，进群看公告，谢谢配合

不修改昵称会被不定期踢除，谢谢配合！

原文地址

http://www.sapjx.com/for-all-entries-optimizing.html

该语法在生成 Native SQL 时会根据内表数据将 Open SQL 拆分成多条在数据库中执行,在内表数据量较大的时候会发生性能问题.

优化 FOR ALL ENTRIES 可以在全局配置参数文件;

针对单独的程序可以使用

HINT 语句

覆盖参数文件的值。

使用

HINT

可以强行指定 FOR ALL ENTRIES 解析使用的参数：

SELECT
*

FROM
[..]

FOR
ALL
ENTRIES
IN
[..]

WHERE
[..]

%_HINTS ORACLE
'&prefer_in_itab_opt 1&&prefer_fix_blocking -1&'

.

通过配置参数文件可以调整 FOR ALL ENTRIES 的性能（使用

TCODE:

RZ10 RZ11

可以查看和调整SAP参数）

1). rsdb/prefer_join

使用连接(JOIN)的方式实现 FOR ALL ENTRIES,

0 – 禁用 1 – 启用

NETWEAVER 7.0 版本 DB2 和 MS SQL Sever实现了该参数,

NETWEAVER 7.1 版本加入了对 Oracle的实现

2) rsdb/prefer_union_all

(

rsdb/prefer_join = 1

时,该参数无效 )

rsdb/prefer_union_all = 0 时, 条件之间用

OR

连接

rsdb/prefer_union_all = 1 时, 条件之间用

UNION ALL

连接

rsdb/prefer_union_all = 0

=>

SELECT
...

WHERE
f = itab[1]-f

OR
f = itab[2]-f

...

OR
f = itab[N]-f

.

rsdb/prefer_union_all = 1

=>

SELECT
...

WHERE
f = itab[1]-f

UNION
ALL
SELECT
...
WHERE
f = itab[2]-f

....

UNION
ALL
SELECT
...
WHERE
f = itab[N]-f

.

Example CODE:

SELECT
* UP TO 30 ROWS

INTO
TABLE
lt_bkpf

FROM
bkpf.

SELECT
*

INTO
TABLE
lt_bseg

FROM
bsas

FOR
ALL
ENTRIES
IN
lt_bkpf

WHERE
bukrs = lt_bkpf-bukrs

AND
belnr = lt_bkpf-belnr

AND
gjahr = lt_bkpf-gjahr

%_HINTS ORACLE
'&prefer_union_all 1&&max_blocking_factor 7&'

.

ST05

跟踪结果：

………

SQL Statement:

SELECT

*

FROM

“BSAS”

WHERE

“MANDT” = :A0 AND “BUKRS” = :A1 AND “BELNR” = :A2 AND “GJAHR” = :A3

UNION ALL SELECT * FROM “BSAS” WHERE “MANDT” = :A4 AND “BUKRS” = :A5 AND “BELNR” = :A6 AND “GJAHR” = :A7

UNION ALL SELECT * FROM “BSAS” WHERE “MANDT” = :A8 AND “BUKRS” = :A9 AND “BELNR” = :A10 AND “GJAHR” = :A11

UNION ALL SELECT * FROM “BSAS” WHERE “MANDT” = :A12 AND “BUKRS” = :A13 AND “BELNR” = :A14 AND “GJAHR” = :A15

UNION ALL SELECT * FROM “BSAS” WHERE “MANDT” = :A16 AND “BUKRS” = :A17 AND “BELNR” = :A18 AND “GJAHR” = :A19

UNION ALL SELECT * FROM “BSAS” WHERE “MANDT” = :A20 AND “BUKRS” = :A21 AND “BELNR” = :A22 AND “GJAHR” = :A23

UNION ALL SELECT * FROM “BSAS” WHERE “MANDT” = :A24 AND “BUKRS” = :A25 AND “BELNR” = :A26 AND “GJAHR” = :A27

3) rsdb/prefer_in_itab_opt

rsdb/prefer_in_itab_opt 该参数被设置为 1，

如果 where 条件中仅有一个字段被内表限制,则将内表的字段放到 IN 条件中。

SQL IN 条件比 OR 效率高, 启用该参数可以提高效率

SELECT
...

FOR
ALL
ENTRIES
IN
itab

WHERE
f = itab-f.

会生成下面的 SQL 语句：

SELECT
...

WHERE
f

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

Example1 – 启用 prefer_in_itab_opt:

DATA
: lt_bkpf
TYPE
TABLE
OF
bkpf,

lt_bseg
TYPE
TABLE
OF
bsas.

DATA
: lv_bkpf
TYPE
i,

lv_bseg
TYPE
i.

SELECT
* UP TO 30 ROWS

INTO
TABLE
lt_bkpf

FROM
bkpf.

SELECT
*

INTO
TABLE
lt_bseg

FROM
bsas

FOR
ALL
ENTRIES
IN
lt_bkpf

WHERE
belnr = lt_bkpf-belnr

%_HINTS ORACLE
'&prefer_in_itab_opt 1&&max_blocking_factor 11&'

.

ST05

跟踪结果:

…………

SQL Statement:

SELECT

*

FROM

“BSAS”

WHERE

“MANDT” = :A0 AND “BELNR” IN ( :A1 , :A2 , :A3 , :A4 , :A5 )

Example2 – 禁用prefer_in_itab_opt

DATA
: lt_bkpf
TYPE
TABLE
OF
bkpf,

lt_bseg
TYPE
TABLE
OF
bsas.

DATA
: lv_bkpf
TYPE
i,

lv_bseg
TYPE
i.

SELECT
*

UP
TO
30 ROWS

INTO
TABLE
lt_bkpf

FROM
bkpf.

SELECT
*

INTO
TABLE
lt_bseg

FROM
bsas

FOR
ALL
ENTRIES
IN
lt_bkpf

WHERE
belnr = lt_bkpf-belnr

%_HINTS ORACLE
'&prefer_in_itab_opt 0&&max_blocking_factor 11&'

.

ST05

跟踪结果:

………

SQL Statement:

SELECT

*

FROM

“BSAS”

WHERE

“MANDT” = :A0 AND “BELNR” = :A1

OR “MANDT” = :A2 AND “BELNR” = :A3

OR “MANDT” = :A4 AND “BELNR” = :A5

OR “MANDT” = :A6 AND “BELNR” = :A7

OR “MANDT” = :A8 AND “BELNR” = :A9

OR “MANDT” = :A10 AND “BELNR” = :A11

OR “MANDT” = :A12 AND “BELNR” = :A13

OR “MANDT” = :A14 AND “BELNR” = :A15

OR “MANDT” = :A16 AND “BELNR” = :A17

OR “MANDT” = :A18 AND “BELNR” = :A19

OR “MANDT” = :A20 AND “BELNR” = :A21

4) rsdb/max_blocking_factor

该参数设定每个SQL Statement 处理的内表条件的个数。

如果内表数据为 50条,

rsdb/max_blocking_factor = 5

则实际生成10条 SQL Statement,每个 SQL Statement 包含5组条件。

Example:

DATA
: lt_bkpf
TYPE
TABLE
OF
bkpf,

lt_bseg
TYPE
TABLE
OF
bsas.

DATA
: lv_bkpf
TYPE
i,

lv_bseg
TYPE
i.

SELECT
*

UP
TO
30 ROWS

INTO
TABLE
lt_bkpf

FROM
bkpf.

SELECT
*

INTO
TABLE
lt_bseg

FROM
bsas

FOR
ALL
ENTRIES
IN
lt_bkpf

WHERE
bukrs = lt_bkpf-bukrs

AND
belnr = lt_bkpf-belnr

AND
gjahr = lt_bkpf-gjahr

%_HINTS ORACLE
'&max_blocking_factor 7&'

.

ST05

跟踪结果:

……..

SQL Statement:

SELECT

*

FROM

“BSAS”

WHERE

“MANDT” = :A0 AND “BUKRS” = :A1 AND “BELNR” = :A2 AND “GJAHR” = :A3 OR

“MANDT” = :A4 AND “BUKRS” = :A5 AND “BELNR” = :A6 AND “GJAHR” = :A7 OR

“MANDT” = :A8 AND “BUKRS” = :A9 AND “BELNR” = :A10 AND “GJAHR” = :A11 OR

“MANDT” = :A12 AND “BUKRS” = :A13 AND “BELNR” = :A14 AND “GJAHR” = :A15 OR

“MANDT” = :A16 AND “BUKRS” = :A17 AND “BELNR” = :A18 AND “GJAHR” = :A19 OR

“MANDT” = :A20 AND “BUKRS” = :A21 AND “BELNR” = :A22 AND “GJAHR” = :A23 OR

“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

时, 会生成 2 条SQL语句，第一条条件为10个，第二条为9个.

当

rsdb/prefer_fix_blocking = 1

时, 会生成 3 条SQL语句，第一条条件为10个，第二条和第三条为5个

Oracle在解析 SQL 时可以使用替换变量,相同的 SQL 在执行时,不需要重新解析,可以提高性能,设置该参数可以保证 SQL Statement 相同。

rsdb/prefer_fix_blocking

和

rsdb/min_in_blocking_factor

针对少量数据；

合理设置可有效利用 Oracle 的替代变量,在使用

HINT

优化大量数据时指定这两个参数的意义不大。

rsdb/max_blocking_factor

设置的过大可能会使 SQL 超长,造成程序 DUMP,故参数文件中应谨慎设置；

在

HINT

中

rsdb/max_blocking_factor

可以设置的大些,但要注意需测试出合理的值,提高性能的同时避免程序DUMP,

实际使用中 ECC5 SQL Statement中最多包含320组条件,即使

max_blocking_factor

设置为1000，

实际仍按320处簇表无法利用 max_blocking_factor参数提高性能,在 ECC5测试,取BSEG每次仅处理一条数据

你可能也喜欢