About rsdb/prefer_join and rsdb_prefer_union_all

[former_member221745]

Hi,

I have a question about the behaviour of these two parameters.

I search the threads in this forum, and found one comment from Frank as below:

"In the 7.0 Kernel we do not use the UNION ALL transformation for FAE statements (rsdb/prefer_union_all=1 ). Instead of this we use

a join transformation ( rsdb/prefer_join=1 ). This typically results in shorter SQL statements conpared to UNION ALL."

But I still see UNION ALL transformation for FAE statements in the 7.0 Kernel with setting rsdb/prefer_join to 1 and rsdb/prefer_union_all to 1.

So I don't know how does the DBSL decide whether to use UNION ALL or CAST to do transformation. And what happen if I also set rsdb/prefer_in_itab_opt to 1?

The last question is what's the hidden magic for "prefer_join", does it really prefer _'JOIN'?

[Frank-Martin · Accepted solution]

Hi Cherry,

let me elaborate a little bit more on this topic.

ABAP "FOR ALL ENTRIES" statements join an internal ABAP table with database tables. The semantics is that duplicate values that are fetched from the database are sorted out by the SAP database interface layer ( DBI ) . So there is always some kind of implicit DISTINCT behavior hidden in "FAE" statements. DBI offers four different possible transformations for those statements which are evaluated by DBI in the following order.

1.) IN-list transformation ( if rsdb/prefer_in_itab_opt = 1 )

This can only be applied to statements where only one "FAE" column occurs in the WHERE clause. DB2 LUW typically works well with IN list if they are unrolled to a NLJN. This is encouraged by the registry setting DB2_INLIST_TO_NLJN = 1 .

2.) JOIN transformation ( if rsdb/prefer_join = 1 )

This transformation comes closest to the idea of joining an in memory table with a database table. Duplicate tuples in the input parameters are sorted out immediately. DBI does not apply this transformation if the ABAP statement itself contains a JOIN.

This transformation may cause performance problems on older DB2 Fixpacks as described in note 1091419 .

3.) UNION ALL transformation ( if rsdb/prefer_union_all = 1 )

The DB2 optimizer typically gives a good access plan with this transformation but statements may grow very long and due to the UNION ALL a lot of duplicate values may be fetched from the database and later on sorted out by DBI.

3.) OR transformation

The DB2 optimizer sometimes does not work well with OR statements. For this reason we try to avoid it.

The DB2 LUW database interface library currently passes the following default values to DBI .

rsdb/prefer_in_itab_opt = 0

rsdb/prefer_join = 1

rsdb/prefer_union_all = 1

It may be debatable if "rsdb/prefer_in_itab_opt = 1" would be a good default setting since it avoids mor UNION ALL statements. I have only seen rare cases where IN lists caused problems. In those cases the optimizer did not do the NLJN transformation. You may give it a try.

Unfortunatelly the database interface libraries can not influence the order in which DBI tries to apply the transformations.

If you encounter a SQL statement where a specific transformation causes problems, you may force another transformation via a OPEN SQL hint.

For example:

 SELECT * INTO TABLE OTAB FROM T100
 BYPASSING BUFFER
 FOR ALL ENTRIES IN ITAB
 WHERE ARBGB = ITAB-ARBGB
 %_HINTS DB6 '&prefer_in_itab_opt 1&'. 

Hope this answers your questions and may also help for other customers.

Regards

Frank

[former_member221745]

Very helpful explanation indeed! Thank you, Frank