FOR ALL ENTRIES 的效率问题 FOR ALL ENTRIES vs DB2 JOIN All abap programers and most of the dba's that support abap programmers are familiar with the abap clause "for all entries". Most of the web pages I visited recently, discuss 3 major drawbacks of the "for all entries" clause: 1. duplicate rows are automatically removed 2. if the itab used in the clause is empty , all the rows in the source table will be selected . 3. performance degradation when using the clause on big tables. In this post I'd like to shed some light on the third issue. Specifically i'll discuss the use of the "for all entries" clause as a means to join tables in the abap code instead of in db2. Say for example you have the following abap code: Select * from mara For all entries in itab Where matnr = itab-matnr. If the actual source of the material list (represented here by itab) is actually another database table, like: select matnr from mseg into corresponding fields of table itab where ? Then you could have used one sql statement that joins both tables. Select t1.* From mara t1, mseg t2 Where t1.matnr = t2.matnr And T2?. So what are the drawbacks of using the "for all entires" instead of a join ? At run time , in order to fulfill the "for all entries " request, the abap engine will generate several sql statements (for detailed information on this refer to note 48230). Regardless of which method the engine uses (union all, "or" or "in" predicates) If the itab is bigger then a few records, the abap engine will break the itab into parts, and rerun an sql statement several times in a loop. This rerun of the same sql statement , each time with different host values, is a source of resource w...
