Radix index scan

 

A radix index scan operation is used to retrieve the rows from a table in a keyed sequence. Like a Table Scan, all of the rows in the index will be sequentially processed, but the resulting row numbers will be sequenced based upon the key columns.

The sequenced rows can be used by the optimizer to satisfy a portion of the query request (such as ordering or grouping). They can be also used to provide faster throughput by performing selection against the index keys rather than all the rows in the table. Since the I/Os associated with the index will only contain the index keys, typically more rows can be paged into memory in one I/O against the index than the rows from a table with a large number of columns.

Table 1. Radix index scan attributes
Data access method Radix index scan
Description Sequentially scan and process all of the keys associated with the index. Any selection is applied to every key value of the index before a table row
Advantages

  • Only those index entries that match any selection continue to be processed

  • Potential to extract all of the data from the index keys' values, thus eliminating the need for a Table Probe

  • Returns the rows back in a sequence based upon the keys of the index
Considerations Generally requires a Table Probe to be performed to extract any remaining columns required to satisfy the query. Can perform poorly when a large number of rows are selected because of the random I/O associated with the Table Probe.
Likely to be used

  • When asking for or expecting only a few rows to be returned from the index

  • When sequencing the rows is required for the query (for example, ordering or grouping)

  • When the selection columns cannot be matched against the leading key columns of the index
Example SQL statement 
CREATE INDEX X1 ON Employee (LastName, WorkDept)

SELECT * FROM Employee WHERE WorkDept BETWEEN 'A01' AND 'E01'
ORDER BY LastName OPTIMIZE FOR 30 ROWS
Messages indicating use

  • Optimizer Debug: 
     CPI4328 -- Access path of file X1 was used by query.

  • PRTSQLINF: 
     SQL4008 -- Index X1 used for table 1.
SMP parallel enabled Yes
Also referred to as Index Scan

Index Scan, Preload

Index Scan, Distinct

Index Scan Distinct, Preload

Index Scan, Key Selection

Visual Explain icon

Radix index scan icon

 

Parent topic:

Radix index

 

Related reference


Effects of the ALWCPYDTA parameter on database performance