Table space scan access (ACCESSTYPE='R' and PREFETCH='S')

DB2® sometimes needs to use a table space scan to access the data, usually because index access is not available for some reason.

Begin program-specific programming interface information.
Table space scan is most often used for one of the following reasons:

  • Access is through a created temporary table. (Index access is not possible for created temporary tables.)
  • A matching index scan is not possible because an index is not available, or no predicates match the index columns.
  • A high percentage of the rows in the table is returned. In this case, an index is not really useful because most rows need to be read anyway.
  • The indexes that have matching predicates have low cluster ratios and are therefore efficient only for small amounts of data.

In some cases, a table space scan is used in combination with a sparse index. A sparse index is created from the initial table space scan, and subsequent access to the table from the same statement uses the sparse index instead of repeatedly scanning the table.

Example

Assume that table T has no index on C1. The following is an example that uses a table space scan:

SELECT * FROM T WHERE C1 = VALUE;

In this case, at least every row in table T must be examined to determine whether the value of C1 matches the given value.

Table space scans of nonsegmented table spaces

DB2 reads and examines every page in the table space, regardless of which table the page belongs to. It might also read pages that have been left as free space and space not yet reclaimed after deleting data.

Table space scans of segmented table spaces

If the table space is segmented, DB2 first determines which segments need to be read. It then reads only the segments in the table space that contains rows of table T. If the prefetch quantity, which is determined by the size of your buffer pool, is greater than the SEGSIZE and if the segments for table T are not contiguous, DB2 might read unnecessary pages. Use a SEGSIZE value that is as large as possible, consistent with the size of the data. A large SEGSIZE value is best to maintain clustering of data rows. For very small tables, specify a SEGSIZE value that is equal to the number of pages required for the table.

The following table summarizes the recommended values for SEGSIZE, depending on how large the table is.
Table 1. Recommendations for SEGSIZE
Number of pages SEGSIZE recommendation
≤ 28 4 to 28
> 28 < 128 pages 32
≥ 128 pages 64

Table space scans of partitioned table spaces

A table space scan on a partitioned table space can be more efficient than on a nonpartitioned table space because DB2 can use page range screening to limit access to only the required partitions. This type of access, which is sometimes called limited partition scan, requires predicates on the partitioning columns.

Table space scans and sequential prefetch

Regardless of the type of table space, DB2 plans to use sequential prefetch for a table space scan. For a segmented table space, DB2 might not actually use sequential prefetch at execution time if it can determine that fewer than four data pages need to be accessed.

If you do not want to use sequential prefetch for a particular query, consider adding the OPTIMIZE FOR 1 ROW clause to the query.

End program-specific programming interface information.