explain paln的

AND-EQUAL

.

Operation accepting multiple sets of rowids, returning the intersection of the sets, eliminating duplicates. Used for the single-column indexes access path.

BITMAP

CONVERSION

TO ROWIDS converts bitmap representations to actual rowids that can be used to access the table.

FROM ROWIDS converts the rowids to a bitmap representation.

COUNT returns the number of rowids if the actual values are not needed.

BITMAP

INDEX

SINGLE VALUE looks up the bitmap for a single key value in the index.

RANGE SCAN retrieves bitmaps for a key value range.

FULL SCAN performs a full scan of a bitmap index if there is no start or stop key.

BITMAP

MERGE

Merges several bitmaps resulting from a range scan into one bitmap.

BITMAP

MINUS

Subtracts bits of one bitmap from another. Row source is used for negated predicates. Can be used only if there are nonnegated predicates yielding a bitmap from which the subtraction can take place. An example appears in "Viewing Bitmap Indexes with EXPLAIN PLAN".

BITMAP

OR

Computes the bitwise OR of two bitmaps.

BITMAP

AND

Computes the bitwise AND of two bitmaps.

BITMAP

KEY ITERATION

Takes each row from a table row source and finds the corresponding bitmap from a bitmap index. This set of bitmaps are then merged into one bitmap in a following BITMAP MERGE operation.

CONNECT BY

.

Retrieves rows in hierarchical order for a query containing a CONNECT BY clause.

CONCATENATION

.

Operation accepting multiple sets of rows returning the union-all of the sets.

COUNT

.

Operation counting the number of rows selected from a table.

STOPKEY

Count operation where the number of rows returned is limited by the ROWNUM expression in the WHERE clause.

DOMAIN INDEX

.

Retrieval of one or more rowids from a domain index. The options column contain information supplied by a user-defined domain index cost function, if any.

FILTER

.

Operation accepting a set of rows, eliminates some of them, and returns the rest.

FIRST ROW

.

Retrieval of only the first row selected by a query.

FOR UPDATE

.

Operation retrieving and locking the rows selected by a query containing a FOR UPDATE clause.

HASH JOIN

(These are join operations.)

.

Operation joining two sets of rows and returning the result. This join method is useful for joining large data sets of data (DSS, Batch). The join condition is an efficient way of accessing the second table.

CBO uses the smaller of the two tables/data sources to build a hash table on the join key in memory. Then it scans the larger table, probing the hash table to find the joined rows.

HASH JOIN

ANTI

Hash anti-join.

HASH JOIN

SEMI

Hash semi-join.

INDEX

(These are access methods.)

UNIQUE SCAN

Retrieval of a single rowid from an index.

INDEX

RANGE SCAN

Retrieval of one or more rowids from an index. Indexed values are scanned in ascending order.

INDEX

RANGE SCAN DESCENDING

Retrieval of one or more rowids from an index. Indexed values are scanned in descending order.

INDEX

FULL SCAN

Retrieval of all rowids from an index when there is no start or stop key. Indexed values are scanned in ascending order.

INDEX

FULL SCAN DESCENDING

Retrieval of all rowids from an index when there is no start or stop key. Indexed values are scanned in descending order.

INDEX

FAST FULL SCAN

Retrieval of all rowids (and column values) using multiblock reads. No sorting order can be defined. Compares to a full table scan on only the indexed columns. Only available with the cost based optimizer.

INDEX

SKIP SCAN

Retrieval of rowids from a concatenated index without using the leading column(s) in the index. Introduced in Oracle9i. Only available with the cost based optimizer.

INLIST ITERATOR

.

Iterates over the next operation in the plan for each value in the IN-list predicate.

INTERSECTION

.

Operation accepting two sets of rows and returning the intersection of the sets, eliminating duplicates.

MERGE JOIN

(These are join operations.)

.

Operation accepting two sets of rows, each sorted by a specific value, combining each row from one set with the matching rows from the other, and returning the result.

MERGE JOIN

OUTER

Merge join operation to perform an outer join statement.

MERGE JOIN

ANTI

Merge anti-join.

MERGE JOIN

SEMI

Merge semi-join.

MERGE JOIN

CARTESIAN

Can result from 1 or more of the tables not having any join conditions to any other tables in the statement. Can occur even with a join and it may not be flagged as CARTESIAN in the plan.

CONNECT BY

.

Retrieval of rows in hierarchical order for a query containing a CONNECT BY clause.

MINUS

.

Operation accepting two sets of rows and returning rows appearing in the first set but not in the second, eliminating duplicates.

NESTED LOOPS

(These are join operations.)

.

Operation accepting two sets of rows, an outer set and an inner set. Oracle compares each row of the outer set with each row of the inner set, returning rows that satisfy a condition. This join method is useful for joining small subsets of data (OLTP). The join condition is an efficient way of accessing the second table.

NESTED LOOPS

OUTER

Nested loops operation to perform an outer join statement.

PARTITION

SINGLE

Access one partition.

PARTITION

ITERATOR

Access many partitions (a subset).

PARTITION

ALL

Access all partitions.

PARTITION

INLIST

Similar to iterator, but based on an IN-list predicate.

PARTITION

INVALID

Indicates that the partition set to be accessed is empty.

Iterates over the next operation in the plan for each partition in the range given by the PARTITION_START and PARTITION_STOP columns. PARTITION describes partition boundaries applicable to a single partitioned object (table or index) or to a set of equi-partitioned objects (a partitioned table and its local indexes). The partition boundaries are provided by the values of PARTITION_START and PARTITION_STOP of the PARTITION. Refer to Table 9-1 for valid values of partition start/stop.

REMOTE

.

Retrieval of data from a remote database.

SEQUENCE

.

Operation involving accessing values of a sequence.

SORT

AGGREGATE

Retrieval of a single row that is the result of applying a group function to a group of selected rows.

SORT

UNIQUE

Operation sorting a set of rows to eliminate duplicates.

SORT

GROUP BY

Operation sorting a set of rows into groups for a query with a GROUP BY clause.

SORT

JOIN

Operation sorting a set of rows before a merge-join.

SORT

ORDER BY

Operation sorting a set of rows for a query with an ORDER BY clause.

TABLE ACCESS

(These are access methods.)

FULL

Retrieval of all rows from a table.

TABLE ACCESS

SAMPLE

Retrieval of sampled rows from a table.

TABLE ACCESS

CLUSTER

Retrieval of rows from a table based on a value of an indexed cluster key.

TABLE ACCESS

HASH

Retrieval of rows from table based on hash cluster key value.

TABLE ACCESS

BY ROWID RANGE

Retrieval of rows from a table based on a rowid range.

TABLE ACCESS

SAMPLE BY ROWID RANGE

Retrieval of sampled rows from a table based on a rowid range.

TABLE ACCESS

BY USER ROWID

If the table rows are located using user-supplied rowids.

TABLE ACCESS

BY INDEX ROWID

If the table is nonpartitioned and rows are located using index(es).

TABLE ACCESS

BY GLOBAL INDEX ROWID

If the table is partitioned and rows are located using only global indexes.

TABLE ACCESS

BY LOCAL INDEX ROWID

If the table is partitioned and rows are located using one or more local indexes and possibly some global indexes.

Partition Boundaries:

The partition boundaries might have been computed by:

A previous PARTITION step, in which case the PARTITION_START and PARTITION_STOP column values replicate the values present in the PARTITION step, and the PARTITION_ID contains the ID of the PARTITION step. Possible values for PARTITION_START and PARTITION_STOP are NUMBER(n), KEY, INVALID.

The TABLE ACCESS or INDEX step itself, in which case the PARTITION_ID contains the ID of the step. Possible values for PARTITION_START and PARTITION_STOP are NUMBER(n), KEY, ROW REMOVE_LOCATION (TABLE ACCESS only), and INVALID.

UNION

.

Operation accepting two sets of rows and returns the union of the sets, eliminating duplicates.

VIEW

.

Operation performing a view's query and then returning the resulting rows to another operation.