SQL Optimizer for Oracle 10.0 - User Guide

FOR UPDATE

Execution Plan image:   

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:   

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:   

Index operation.

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

FULL INDEX SCAN (MIN/MAX)

Execution Plan image:   

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

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

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.