Table scan access method

All rows in the table are read. The selection criteria are applied to each row, and only the rows that match the criteria are returned to the calling application. The rows in the table are processed in no guaranteed order. If you want the result in a particular sequence, you must specify the ORDER BY clause (or OPNQRYF KEYFLD parameter).

Table scan can be very efficient for the following reasons:

• It minimizes the number of page I/O operations because all rows in a given page are processed, and once the page is in main storage, the page is not retrieved again.
• The database manager can easily predict the sequence of pages from the dataspace for retrieval. For this reason, the database manager can schedule asynchronous I/O of the pages into main storage from auxiliary storage. This is commonly referred to as pre-fetching. This is done so that the page is available in main storage when the database manager needs to access the data.

Where the table scan access method is most effective

This selection method is very good when a large percentage of the rows is to be selected. A large percentage is generally 20% or more.

Table scan processing can be adversely affected when rows are selected from a table that contains deleted rows. This is because the delete operation only marks rows as deleted. For table scan processing, the database manager reads all of the deleted rows, even though none of the deleted rows are ever selected. You should use the Reorganize Physical File Member (RGZPFM) CL command to eliminate deleted rows. Specifying REUSEDLT(*YES) on the physical file can also reuse the deleted row space. SQL tables are created with REUSEDLT(*YES).

Where the table scan access method is least effective

Table scan processing is not very efficient when a small percentage of rows in the table will be selected. Because all rows in the table are examined, this leads to unnecessary use of I/O and processing unit resources.

PRTSQLINF command messages

The messages created by the PRTSQLINF CL command to describe a query in an SQL program which is using the dataspace selection method would appear as follows:

SQL4010  Table scan access for table 1.

Selection algorithms for database scan access method

The Licensed Internal Code can use one of two algorithms for selection when a table scan is processed, derived column selection and dataspace element selection.

The following pseudocode illustrates the derived column selection algorithm:

   DO UNTIL END OF TABLE
 
   1. Address the next (or first) row
 
   2. Map all column values to an internal buffer, performing all derived
      operations.
 
   3. Evaluate the selection criteria to a TRUE or FALSE value using
      the column values as they were copied to internal buffer.
 
   4. IF the selection is TRUE
      THEN
        Copy the values from the internal buffer into the
        user's answer buffer.
      ELSE
        No operation
   END

The table scan selection algorithm is as follows:

   DO UNTIL END OF TABLE
 
   1. Calculate a search limit. This limit is usually the number of
      rows which are already in active memory, or have already
      had an I/O request done to be loaded into memory.
 
   2. DO UNTIL (search limit reached
      or row selection criteria is TRUE)
      a. Address the next (or first) row
 
      b. Evaluate any selection criteria which does not
         require a derived value directly for the dataspace
         row.
 
      END
 
   3. IF the selection is true
      THEN
      a. Map all column values to an internal buffer, performing all
         derived operations.
 
      b. Copy the values from the internal buffer into the
         user's answer buffer.
      ELSE
        No operation
   END

The table scan selection algorithm provides better performance than derived column selection for two reasons:

• Data movement and computations are only done on rows which are selected.
• The loop in step 2 of the table scan selection algorithm is generated into an executable code burst. When a small percentage of rows is actually selected, DB2 UDB for AS/400 will be running this very small program until a row is found.

Guidelines for coding queries

No action is necessary for queries that use the table scan selection algorithm of the table scan access method. Any query interface can utilize this improvement. However, the following guidelines determine whether a selection predicate can be implemented as dataspace selection:

• Neither operand of the predicate can be of any kind of a derived value, function, substring, concatenation, or numeric expression.
• When both operands of a selection predicate are numeric columns, both columns must have the same type, scale, and precision; otherwise, one operand is mapped into a derived value. For example, a DECIMAL(3,1) must only be compared against another DECIMAL(3,1) column.
• When one operand of a selection predicate is a numeric column and the other is a constant or host variable, then the types must be the same and the precision and scale of the constant/host variable must be less than or equal to that of the column.
• Selection predicates involving packed decimal or numeric types of columns can only be done if the table was created by the SQL CREATE TABLE statement.
• A varying length character column cannot be referenced in the selection predicate.
• When one operand of a selection predicate is a character column and the other is a constant or host variable, then the length of the host variable cannot be greater than that of the column.
• Comparison of character column data must not require CCSID or key board shift translation.

It can be important to avoid derived column selection because the reduction in CPU and response time for table scan selection can be large, in some cases as high as 70-80%. The queries that will benefit the most from dataspace selection are those where less than 60% of the table is actually selected. The lower the percentage of rows selected, the more noticeable the performance benefit will be.