Statements Bypassing the Buffer

More complex queries are passed onto the database server instead of using the table buffer. Such queries are the following:

• Any SELECT with a sub query or with joins

• Any set function (COUNT, MIN, MAX, SUM, AVG)

• GROUP BY

• HAVING

• SELECT DISTINCT

• ORDER BY (other than PRIMARY KEY) - the data stored in the buffer is always ordered by the primary key. Any other ordering must be performed by the database.

Additionally, the following statement that locks the selected rows is executed by the database:

• SELECT FOR UPDATE

The table buffer is also bypassed when the isolation level is other than READ_UNCOMMITTED. The READ_UNCOMMITTED level implies that other transactions can view uncommitted changes in the table buffer. For other isolation levels the data must be committed, and it is read directly from the database because the table buffer operates in “dirty read” mode.

Finally, sometimes it could be necessary to read from the database to avoid any uncertainty of out-dated data (what is discussed in the next section). Therefore it is possible to add “BYPASSING BUFFER” as a comment within a SELECT statement to assure that the request is not handled by the buffer but by the database.

As an example we consider a table MYTAB with the key fields KEY1, KEY2, and KEY3 and the data field DATA. Let us assume the granularity of the buffer key range to be KEY1, KEY2.

Queries Bypassing the Buffer

            SELECT /*@ SAP BYPASSING BUFFER */ * FROM MYTAB 
    WHERE KEY1 = ? AND KEY2 = ?

SELECT * FROM MYTAB WHERE KEY1 = ?

SELECT * FROM MYTAB 
    WHERE KEY1 = ? AND KEY2 = ? OR KEY1 = ?

SELECT MAX(DATA) FROM MYTAB WHERE KEY1 = ? KEY2 = ?

SELECT * FROM MYTAB 
    WHERE KEY1 = ? AND KEY2 = ? AND DATA = 
   (SELECT DATA FROM MYTAB WHERE KEY1 = ? AND KEY2 = ?)