Chat now with support
Chat with 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 SKIP SCAN

Index operation.

INDEX SKIP SCAN selects rows from a concatenated index without using the leading columns in the index. This feature was introduced in Oracle 9i.

 

Related Topics

INDEX SKIP SCAN DESCENDING

 

The INDEX SKIP SCAN DESCENDING operation retrieves rows from a concatenated index without using the leading column in descending order.

 

  

 

INDEX UNIQUE SCAN

Execution Plan image: image\Plan_Index_Unique_Scan.gif   

Index operation.

INDEX UNIQUE SCAN, which selects a unique value from a unique index, is the most efficient method of selecting a row from known field values.

Each unique index access is built from a separate access into the indexs B*-tree structure, drilling down from the index root to the leaf blocks. On average, three blocks are read to fulfill the unique index access.

Example

select Name, City, State
from COMPANY
where Company_ID = 12345;

Execution Plan

TABLE ACCESS BY ROWID COMPANY
INDEX UNIQUE SCAN COMPANY_PK

Interpreting the Execution Plan

The query uses the COMPANY_ID column as the sole criteria in its WHERE clause. Since COMPANY_ID is the primary key of the COMPANY table, it has a unique index associated with it. The unique index for the COMPANY_ID primary key is named COMPANY_PK.

During the query, the COMPANY_PK index is scanned for one COMPANY_ID value (12345). When the COMPANY_ID value is found, the ROWID associated with that COMPANY_ID is used to query the COMPANY table.

Tuning Advice

Avoid Unhelpful Index Scans

 

Related Topics

INLIST ITERATOR

Execution Plan image: image\Plan_InList_Iterator.gif

Row operation.

An INLIST ITERATOR operation displays in the Execution Plan output if an index implements an INLIST predicate.

Example

SELECT * FROM emp WHERE empno IN (7876, 7900, 7902);

Execution Plan

Operation

Options

Object_name

SELECT STATEMENT

 

 

INLIST ITERATOR

 

 

TABLE ACCESS

BY ROWID

EMP

INDEX

RANGE SCAN

EMP_EMPNO

The INLIST ITERATOR operation iterates over the operation below it for each value in the IN-list predicate. For partitioned tables and indexes, the three possible types of INLIST columns are:

  • Index column

  • Index and partition column

  • Partition column

Index Column

When the INLIST column empno is an index column but not a partition column, the plan is as follows (the INLIST operator displays above the table operation but below the partition operation):

Operation

Options

Object_name

Partition Start

Partition Stop

SELECT STATEMENT

 

 

 

 

PARTITION

INLIST

 

KEY(INLIST)

KEY(INLIST)

INLIST ITERATOR

 

 

 

 

TABLE ACCESS

BY ROWID

EMP

KEY(INLIST)

KEY(INLIST)

INDEX

RANGE SCAN

EMP_EMPNO

KEY(INLIST)

KEY(INLIST)

The KEY(INLIST) designation for the partition start and stop keys specifies that an INLIST predicate displays on the index start/stop keys.

Index and Partition Columns

When empno is an indexed and a partition column, the Execution plan contains an INLIST ITERATOR operation above the partition operation:

Operation

Options

Object_name

Partition Start

Partition Stop

SELECT STATEMENT

 

 

 

 

INLIST ITERATOR

 

 

 

 

PARTITION

ITERATOR

 

KEY(INLIST)

KEY(INLIST)

TABLE ACCESS

BY ROWID

EMP

KEY(INLIST)

KEY(INLIST)

INDEX

RANGE SCAN

EMP_EMPNO

KEY(INLIST)

KEY(INLIST)

Partition Column

When empno is a partition column and there are no indexes, no INLIST ITERATOR operation is allocated:

Operation

Options

Object_name

Partition Start

Partition Stop

SELECT STATEMENT

 

 

 

 

PARTITION

 

 

KEY(INLIST)

KEY(INLIST)

TABLE ACCESS

BY ROWID

EMP

KEY(INLIST)

KEY(INLIST)

INDEX

RANGE SCAN

EMP_EMPNO

KEY(INLIST)

KEY(INLIST)

When emp_empno is a bitmap index, the Execution plan is as follows:

Operation

Options

Object_name

SELECT STATEMENT

 

 

INLIST ITERATOR

 

 

TABLE ACCESS

BY INDEX ROWID

EMP

BITMAP CONVERSION

 

TO ROWIDS

BITMAP INDEX

SINGLE VALUE

EMP_EMPNO

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating