Radix index
An SQL index (or keyed sequence access path) is a permanent object that is created over a table and used by the optimizer to provide a sequenced view of the data for a scan or probe operation.
The rows in the tables are sequenced in the index based upon the key columns specified on the creation of the object. When the key columns are matched up by the optimizer to a query, it gives the optimizer the ability to use the radix index to help satisfy any selection, ordering, grouping or join requirements.
Typically the use of an index operation will also include a Table Probe operation to provide access to any columns needed to satisfy the query that cannot be found as index keys. If all of the columns necessary to satisfy the query request for a table can be found as keys of an index, then the Table Probe is not required and the query uses Index Only Access. Avoiding the Table Probe can be an important savings for a query. The I/O associated with a Table Probe is typically the more expensive synchronous random I/O.
Visual Explain icon:
- 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. - Radix index probe
A radix index probe operation is used to retrieve the rows from a table in a keyed sequence. The main difference between the Radix Index Probe and the Radix Index Scan is that the rows being returned must first be identified by a probe operation to subset the rows being retrieved.