Chat now with support
Chat mit Support

SQL Optimizer for Oracle 10.0 - User Guide

Welcome to SQL Optimizer
About SQL Optimizer SQL Optimization Workflow New in This Release Additional Resources Database Privileges Database Privileges Script Connect to the Database Windows Layout Customize Toolbars Keyboard Shortcuts Support Bundle Register SQL Optimizer Check for Updates SQL Operations
ALL PARTITION ALTER INDEX AND EQUAL ANTI JOIN BITMAP AND BITMAP COMPACTION BITMAP CONSTRUCTION BITMAP CONVERSION BITMAP INDEX BITMAP JOIN INDEX UPDATE BITMAP JOIN INDEX UPDATE STATEMENT BITMAP KEY ITERATION BITMAP MERGE BITMAP MINUS BITMAP OR BUFFER SORT CARTESIAN JOIN COLLECTION ITERATOR CONCATENATION CONNECT BY CONNECT BY PUMP COUNT COUNT STOPKEY CREATE AS SELECT CUBE SCAN DDL STATEMENT DELETE DOMAIN INDEX FAST FULL INDEX SCAN FILTER FIRST ROWS FIXED INDEX FIXED TABLE FOR UPDATE FULL INDEX SCAN FULL INDEX SCAN DESCENDING FULL INDEX SCAN (MIN/MAX) HASH GROUP BY HASH GROUP BY PIVOT HASH JOIN HASH JOIN BUFFERED HASH PARTITION HASH UNIQUE INDEX INDEX BUILD NON UNIQUE INDEX RANGE SCAN INDEX RANGE SCAN DESCENDING INDEX RANGE SCAN (MIN/MAX) INDEX SAMPLE FAST FULL SCAN INDEX SKIP SCAN INDEX SKIP SCAN DESCENDING INDEX UNIQUE SCAN INLIST ITERATOR INLIST PARTITION INSERT INTERSECTION INTO INVALID PARTITION ITERATOR PARTITION LOAD AS SELECT MAT_VIEW ACCESS MAT_VIEW REWRITE ACCESS MERGE JOIN MINUS MULTI-TABLE INSERT NESTED LOOPS OUTER JOIN PARTITION PARTITION HASH EMPTY PARTITION LIST PARTITION RANGE PROJECTION PX BLOCK ITERATOR PX COORDINATOR PX ITERATOR PX PARTITION PX PARTITION HASH ALL PX PARTITION LIST ALL PX PARTITION RANGE ALL PX RECEIVE PX SEND RANGE PARTITION RECURSIVE EXECUTION RECURSIVE WITH PUMP REFERENCE MODEL REMOTE SELECT SEMI JOIN SEQUENCE SINGLE PARTITION SINGLE RANGE PARTITION SORT SORT AGGREGATE SORT GROUP BY SORT GROUP BY CUBE SORT GROUP BY NOSORT SORT GROUP BY ROLLUP SORT JOIN SORT ORDER BY SORT UNIQUE SQL MODEL TABLE ACCESS TABLE ACCESS BY GLOBAL INDEX ROWID TABLE ACCESS BY INDEX ROWID TABLE ACCESS BY LOCAL INDEX ROWID TABLE ACCESS BY ROWID TABLE ACCESS BY USER ROWID TABLE ACCESS CLUSTER TABLE ACCESS FULL TABLE ACCESS HASH TABLE ACCESS SAMPLE TABLE QUEUE TEMP TABLE GENERATION TEMP TABLE TRANSFORMATION UNION UNION ALL UNION ALL (RECURSIVE WITH) UNPIVOT UPDATE VIEW VIEW PUSHED PREDICATE WINDOW
Optimize SQL
Create Optimize SQL Sessions Open Optimizer SQL Sessions Rewrite SQL Generate Execution Plan Alternatives
Optimize Indexes Batch Optimize SQL Scan SQL Inspect SGA Analyze Impact Manage Plans Configure Options SQL Optimizer Tutorials About Us Legal Notices

INDEX RANGE SCAN

Execution Plan image: image\Plan_Index_Range_Scan.gif  

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).

Example

select Name, City, State
from COMPANY
where City > 'Roanoke';

Execution Plan

TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$CITY

Interpreting the Execution Plan

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.

 

 

INDEX RANGE SCAN DESCENDING

Execution Plan image: image\Plan_Index_Unique_Scan.gif   

Index operation.

Returns one or more ROWIDs from an index. Indexed values are scanned in descending order.

 

Related Topics

 

INDEX RANGE SCAN (MIN/MAX)

Execution Plan image: image\Plan_Index_Range_Scan.gif  

Index operation.

INDEX RANGE SCAN MIN/MAX selects the highest or lowest entry from a range of values in the index.

 

Related Topics

 

INDEX SAMPLE FAST FULL SCAN

 

The INDEX SAMPLE FAST FULL SCAN operation retrieves rows by performing a fast read on a portion (sample) of the index records.

 

  

 

 

Verwandte Dokumente

The document was helpful.

Bewertung auswählen

I easily found the information I needed.

Bewertung auswählen