SELECT FOR ALL ENTRIES:
With SELECT FOR ALL ENTRIES, you can divide SELECT statements into loops. This variant enables you to create a JOIN between an internal table and a database table. In the example above, only the records from table KKNA1 are read that have customer numbers in the internal table g_itab_vvbak.
The following problems occur with SELECT FOR ALL ENTRIES:
You cannot have an empty driver table. If the driver table is empty, selection is not limited. Note that if the WHERE clause contains conditions that do not refer to the internal driver table, these conditions are not evaluated either.
Duplicate table entries must be deleted from the internal driver table before the SELECT FOR ALL ENTRIES is executed. If they are not deleted, identical data is read unnecessarily from the database.
If SELECT FOR ALL ENTRIES is used, SQL statements are divided into several SELECT statements by the database interface. The individual SELECT statements are processed on the database and the results are buffered in the database interface. The database interface eliminates duplicate data records from the resulting set, and transfers the overall results to ABAP.
Each SELECT statement processed on the database is executed as a concatenation of individual accesses (for example, index range scans or index unique scans). Concatenation is performed over the entries in the internal driver table. Since SELECT FOR ALL ENTRIES can become very complex, you should avoid combining them with SELECT-OPTIONS or RANGES tables.
Exactly how the WHERE clauses of individual SELECT statements are executed by the database depends on the type of database. The link is generated, for example, using an OR operation as IN list.
You can use RANGES tables in ABAP. You can define RANGES tables with the ABAP statements SELECT-OPTIONS and RANGES. These two statements implicitly define an internal table with the fields SIGN, OPTION , LOW and HIGH (see R/3 documentation). The end user at the selection screen dynamically fills a RANGES table defined with SELECT OPTION. The program determines how the RANGES table is filled.
The database interface converts the rows in a RANGES table so that they can be read by the DBMS, and links them with OR. The SQL statement produced is then transferred to the database.
The RANGES table can contain rows with complex expressions (for example, BT => BETWEEN, CP => LIKE). The SQL statement that results from a RANGES table can therefore be complex and difficult for the DBMS to process.
If the program determines how RANGES tables are filled, you must limit the number of entries. If the RANGES table is too large, a complex database statement is generated when the OR or IN list is evaluated. This statement may take a long time to be evaluated on the database. The maximum possible length of the database statement depends on the database (for example, with ORACLE 32 bit version, the maximum is 32 KB). If the maximum length is exceeded, an ABAP dump may be generated
You can use aggregate functions (COUNT, SUM, MAX, MIN, AVG) to perform calculations using the DBMS. Only use aggregate functions if they significantly reduce the quantity of data records to be transferred.
Aggregate functions are usually used used with a GROUP BY clause. SQL statements that contain aggregate functions bypass R/3 table buffers. (Note that this can be a disadvantage if tables are buffered.)
Field values are processed differently, depending on whether they are in ABAP or at database level. The database uses a different method than ABAP for rounding data (for example, when converting F to I). The database recognizes the value NULL. ABAP does not recognize the value NULL.
For the aggregate function AVG, use data type F (number with floating point) for the target field. For the aggregate function SUM, make the data type of the target field longer than the data type of the source field, to allow for overflow.
No comments:
Post a Comment