Execution Plan image:
Index operation.
INDEX RANGE SCAN selects a range of values from an index; the index can be either unique or non-unique. Range scans are used when one of the following conditions are met:
A range operator (such as < or >) is used.
The BETWEEN clause is used.
A search string with a wildcard is used (such as A*).
Only part of a concatenated index is used (such as by using only the leading column of a two-column index).
The access to the range of values within the index starts with an index search for the first row that is included in the range. After the first row has been located, there is a "horizontal" scan of the index blocks until the last row inside the range is found.
Note: The efficiency of an INDEX RANGE SCAN is directly related to two factors: (1) the number of keys in the selected range (the more values, the longer the search), (2) the condition of the index (the more fragmented, the longer the search).
select Name, City, State
from COMPANY
where City > 'Roanoke';
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$CITY
The Execution Plan shows that the index on the City column is used to find ROWIDs in the COMPANY table that satisfy the limiting condition on the City value. Since a range of values is specified City > 'Roanoke', an INDEX RANGE SCAN is performed. The first value that falls within the range is found in the index; the rest of the index is then searched for the remaining values. For each matching value, the ROWID is recorded. The ROWIDs from the INDEX RANGE SCAN are used to query the COMPANY table for the Name and State values.
Execution Plan image:
Index operation.
Returns one or more ROWIDs from an index. Indexed values are scanned in descending order.
Execution Plan image:
Index operation.
INDEX RANGE SCAN MIN/MAX selects the highest or lowest entry from a range of values in the index.
The INDEX SAMPLE FAST FULL SCAN operation retrieves rows by performing a fast read on a portion (sample) of the index records.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Conditions d’utilisation Confidentialité Cookie Preference Center