Table probe

Table probe

A table probe operation is used to retrieve a specific row from a table based upon its row number. The row number is provided to the table probe access method by some other operation that generates a row number for the table.
This can include index operations as well as temporary row number lists or bitmaps. The processing for a table probe is typically random; it requests a small I/O to only retrieve the row in question and does not attempt to bring in any extraneous rows. This leads to very efficient processing for smaller result sets because only the rows needed to satisfy the query are processed rather than the scan method which must process all of the rows. However, since the sequence of the row numbers are not known in advance, very little pre-fetching can be performed to bring the data into main memory. This can result in most of the I/Os associated with this access method to be performed synchronously.
Table 1. Table probe attributes
Data access method Table probe
Description Reads a single row from the table based upon a specific row number. A random I/O is performed against the table to extract the row.
Advantages

Requests smaller I/Os to prevent paging rows into memory that are not needed

Can be used in conjunction with any access method that generates a row number for the table probe to process

Considerations Because of the synchronous random I/O the probe can perform poorly when a large number of rows are selected
Likely to be used

When row numbers (either from indexes or temporary row number lists) are being used, but data from the underlying table rows are required for further processing of the query

When processing any remaining selection or projection of the values
Example SQL statement
 CREATE INDEX X1 ON Employee (LastName)
 
SELECT * FROM Employee 
WHERE WorkDept BETWEEN 'A01' AND 'E01'  
AND LastName IN ('Smith', 'Jones', 'Peterson') 
OPTIMIZE FOR ALL ROWS
Messages indicating use
There is no specific message that indicates the use of a table probe. The messages in this example illustrate the use of a data access method that generates a row number that is used to perform the table probe operation.
Optimizer Debug:
CPI4328 — Access path of file X1 was used by query
PRTSQLINF:
SQL4008 — Index X1 used for table 1.
SQL4011 — Index scan-key row positioning (probe) 
          used on table 1.
SMP parallel enabled Yes
Also referred to as Table Probe, Preload
Visual Explain icon
Parent topic:
Table

Data access method	Table probe
Description	Reads a single row from the table based upon a specific row number. A random I/O is performed against the table to extract the row.
Advantages	Requests smaller I/Os to prevent paging rows into memory that are not needed Can be used in conjunction with any access method that generates a row number for the table probe to process
Considerations	Because of the synchronous random I/O the probe can perform poorly when a large number of rows are selected
Likely to be used	When row numbers (either from indexes or temporary row number lists) are being used, but data from the underlying table rows are required for further processing of the query When processing any remaining selection or projection of the values
Example SQL statement	CREATE INDEX X1 ON Employee (LastName) SELECT * FROM Employee WHERE WorkDept BETWEEN 'A01' AND 'E01' AND LastName IN ('Smith', 'Jones', 'Peterson') OPTIMIZE FOR ALL ROWS
Messages indicating use	There is no specific message that indicates the use of a table probe. The messages in this example illustrate the use of a data access method that generates a row number that is used to perform the table probe operation. Optimizer Debug: CPI4328 — Access path of file X1 was used by query PRTSQLINF: SQL4008 — Index X1 used for table 1. SQL4011 — Index scan-key row positioning (probe) used on table 1.
SMP parallel enabled	Yes
Also referred to as	Table Probe, Preload
Visual Explain icon