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

FOR UPDATE

Execution Plan image: image\Plan_Update_Stmt.gif   

Set operation.

FOR UPDATE places a row-level lock on all the rows that can be retrieved from the SELECT statement.

Using FOR UPDATE allows you to use the WHERE CURRENT OF clause in INSERT, UPDATE, and DELETE commands. A COMMIT will invalidate the cursor, so you will need to reissue the SELECT FOR UPDATE after every commit.

Example

select Name, City, State
from COMPANY
where City > 'Roanoke'
and Active_Flag = 'Y'
for update of Name;

Execution Plan

FOR UPDATE
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 that satisfy the limiting condition on the City value (City > 'Roanoke'). The ROWIDs from the index scan are used to query the COMPANY table for the Name and State values. The Active_Flag='Y' criteria is implicitly applied during the TABLE ACCESS BY ROWID operation. The FOR UPDATE operation is then applied to give the user row-level locks on each row returned from the query.

 

Related Topics

 

FULL INDEX SCAN

Execution Plan image: image\Plan_Index_Full_Scan.gif   

Index operation.

Reading rows in key order requires a block-by-block full scan of the index, which is incompatible with the Fast Full Scan. Although the fast full scan is much more efficient than the "normal" full index scan, the fast full scan does not return rows in index order.

Although using an index can eliminate the need to perform a sort, the overhead of reading all the index blocks and all the table blocks may be greater than the overhead of performing the sort. However, using the index should result in a faster retrieval of the first row since as soon as the row is retrieved it may be returned, whereas the sort approach will require that all rows be retrieved before the first row is returned. As a result, the cost based optimizer will tend to use the index if the optimizer goal is FIRST ROWS, but will select a full table scan if the goal is ALL ROWS.

A way of avoiding both sort and table lookup overhead is to create an index which contains all the columns in the select list as well as the columns in the ORDER BY clause. Oracle can then resolve the query by using an index lookup alone.

Using an index to avoid a sort will lead to vastly superior response time (time to retrieve the first row) but much poorer throughput (time to retrieve the last row).

 

FULL INDEX SCAN DESCENDING

Execution Plan image: image\Plan_Index_Full_Scan.gif   

Index operation.

Finds one or more index entries. Index entries are scanned in descending order.

 

FULL INDEX SCAN (MIN/MAX)

Execution Plan image: image\Plan_Index_Full_Scan_MinMax.gif   

Index operation.

When there is an index on the column that you querying for the maximum or minimum value, you can use that index to rapidly locate the value.

Execution Plan Generated Under Oracle 8.0.6

SELECT MAX (salary)
FROM employees

Rows

Execution Plan

0

SELECT STATEMENT GOAL: CHOOSE

1

SORT (AGGREGATE)

1

INDEX (FULL SCAN) OF 'EMPLOYEE_SALARY_IDX'

A full index scan might or might not be better than a full table scan (a full table scan can read multiple blocks in each I/O) but hardly seems the best course of action. Why can’t Oracle simply go directly to the first or last leaf block to identify the highest or lowest value?

Oracle goes directly to the maximum value in the above case. Note the row count of "1" —although the plan suggests that all the index entries are being read in fact only the maximum value in the first leaf block is accessed.

Execution Plan Generated Under Oracle8i

Rows

Execution Plan

0

SELECT STATEMENT GOAL: CHOOSE

1

SORT (AGGREGATE)

1

INDEX (FULL SCAN (MIN/MAX)) OF 'EMPLOYEE_SAL_IDX'

The full scan (min/max) step indicates that Oracle is going directly to the maximum or minimum value. This is more efficient than performing a full index or full table scan.

Note: The fastest way to get the maximum or minimum value for a column is to have a B*-tree index on that column.

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating