Temporary Indexed List
The temporary indexed list is a temporary object that allows the optimizer to sequence rows based upon a column or set of columns. The temporary indexed list can be scanned by the optimizer to satisfy ordering or grouping requirements of the query.
A temporary indexed list is a data structure that contains a radix index object to provide ordering. This object is generally used when a query contains ordering and non-equal predicates and only a partial answer set is fetched. The object is initially populated with a distinct set of values by distinct scanning or probing an existing index. As processing continues and more rows are needed, additional rows will be inserted into the temporary indexed list for additional processing.
If the underlying index used to build the temporary indexed list contains all the necessary columns to satisfy any further processing, then these columns are built into the temporary indexed list. This population avoids any random I/Os associated with a table probe operation. If the underlying index does not contain all the necessary columns, then a table probe operation is required to recollect the missing columns from the temporary indexed list before the selected rows can be processed.
A temporary indexed list is an internal data structure and can only be created by the database manager. The temporary indexed list is always used in conjunction with the temporary indexed list scan and index merge data access methods.
Visual explain icon:
