Oracle's Common execution planning steps (Description data reference for explain results) ____oracle

Execute explain plain for select in Pl/sql ...

View Description Data reference:

Oracle's common execution planning steps

Here we introduce some common steps and algorithms in the execution plan.

1. Table Access Path

(1) TABLE ACCESS

Full: whole table scan. It accesses each record in the table (reads each block of data within the high watermark).

CLUSTER: The table is accessed through the keys of the indexed cluster.

by INDEX ROWID: A single record in a table is accessed by specifying ROWID. ROWID is the quickest way to access records, usually by index access.

By USER ROWID: Provides a binding variable, a literal variable, or a WHERE CURRENT of cursor clause to access through ROWID.

By Global index ROWID: The ROWID is obtained by a globally partitioned index, and then table access is made. The access appears in the partition table.

by local index ROWID: ROWID is obtained by a locally partitioned index, and then table access is made. The access appears in the partition table.

(2) EXTERNAL table access: Access to external tables.

(3) Result cache: This SQL result set may come from the results set cache.

(4) Mat_view REWRITE Access:sql is rewritten to take advantage of materialized views.

2. Index operation

(1) And-equal: Merges the result sets from one or more indexes.

(2) INDEX

UNIQUE SCAN: An index scan that returns only the address (ROWID) of one record.

RANGE SCAN: Returns index retrieval of ROWID for multiple records. This is generally the case, because the interval operator appears.

Full SCAN: Scans the entire index in the order of the index keys.

SKIP SCAN: Combination index key non-leading column index retrieval.

Full SCAN (Max/min): Retrieves the highest or lowest index entry in the index.

FAST Full SCAN: Scans entries for each index in block order, and may use multiple blocks of reads.

(3) Domain index: Application field indexes.

3. Bitmap indexing operation

(1) BITMAP

Conversion: Converts a bit to rowid or vice versa.

INDEX: A value from a bitmap or a range of values.

Merge: Merging bitmaps.

Minus: Subtracts another bitmap from a bitmap.

Or: An OR operation on two bitmaps.

4. Table JOIN operation

(1) Connect by: Perform a hierarchical self connection operation on the output of the previous step.

(2) Merge join: Performs a merge join for the output of the previous step.

(3) NESTED loop: Performs a nested loop connection to the previous step. For each row of the previous result set, the next level of result set is scanned to find the matching record.

(4) Hash join: Hash joins for two records.

(5) Any connection operation

OUTER: Outer connection

ANTI: Reverse Connection

SEMI: Reverse Connection

Cartesian: Each record in one result set is connected to each record in another result set.

5. Set operation

Concatenation: As with explicitly specifying a UNION clause, multiple result sets are merged in the same way. It usually occurs when an OR operation is used on an indexed column.

Intersection: Compares two results and returns only records that exist in two result sets.

Minus: Returns all records in the first result set except for records that appear in the second result set.

Union-all: Merges two result sets and returns all records in two result set.

UNION: Same as Union-all, but it does not return duplicate records.

View: Access a view definition or create a temporary table to store the result set.

6. Partition operation

(1) PARTITION

Single: Access individual partitions.

Iterator: Access to multiple partitions.

All: access to all partitions.

INLIST: Partitions are accessed based on the values in the in list.

7. Summary operation

(1) Count: Use the Count function to calculate.

Stopkey: A record in a calculated result that stops the calculation when a certain number is reached. This usually occurs when the WHERE clause is used and the rownum is specified, such as where RowNum <= 10.

(2) BUFFER sort: An intrinsic ordering of the temporary result set.

(3) hash GROUP by: Use hashing to group operations.

(4) INLIST iterator: Implements a child operation for each value in in.

(5) SORT

GROUP BY: The result set is sorted to satisfy group by.

AGGREGATE: This action occurs when grouping functions are used on grouped data.

JOIN: Sort the records by merging the joins.

UNIQUE: Excludes duplicate records for sorting operations, usually using the DISTINCT clause.

GROUP BY: The result set is sorted by grouping to satisfy the GROUP BY clause.

8, other operations

(1) For update: The FOR UPDATE clause is used.

(2) COLLECTION iterator: Use the table function to extract records.

(3) FAST DUAL: Access to DUAL table.

(4) FILTER: Excludes records from the result set that do not match the selection criteria.

(5) REMOTE: Access to an external database through a database connection.

(6) Primary ROW: Gets the first record of the query.

(7) SEQUENCE: Oracle sequence is used.

(8) LOAD as SELECT: Inserts with a direct path using SELECT.

(9) Fixed table: Access to stationary table (x$/v$).

(a) fixed index: access to pinned indexes.

(one) WINDOW BUFFER: Supports internal operations of analytic functions.