Shulou(Shulou.com)06/01 Report--

Engaged in Oracle-related work, from the initial look confused to now a little knowledge, but also to sum up their recent learning about the implementation plan of the SQL statement in Oracle. The following is the directory structure of the article:

What is the Oracle implementation plan?

An execution plan is a description of the execution process or access path of a query statement in Oracle

Second, how to view the Oracle implementation plan? 2.1 explain plan for command to view the execution plan

In sql*plus, execute the following command:

1) explain plan for select * from XXXX

2) select * from table (dbms_xplan.display)

2.2 SET AUTOTRACE ON View execution Plan

Syntax: SET AUTOT [RACE] {OFF | ON | TRACE [ONLY]} [explain]] [stats]]

Serial number

Command

explain

one

SET AUTOTRACE OFF

This is the default value, that is, turn off Autotrace

two

SET AUTOTRACE ON EXPLAIN

Show only the execution plan

three

SET AUTOTRACE ON STATISTICS

Show only executed statistics

four

SET AUTOTRACE ON

It contains two items: 2, 2 and 3.

five

SET AUTOTRACE TRACEONLY

Similar to ON, but does not display the execution result of the statement

2.3 PLSQL Developer View execution plan ① configuration execution plan items that need to be displayed:

Tools-> preferences-> window Type-> Plan window-> configure the columns to be displayed in the execution plan as needed

Common column fields for execution plans explain:

Cardinality (Rows): the number of result set rows returned by the current operation estimated by Oracle

Bytes (Bytes): the number of bytes returned after performing this step

COST, CPU cost: the cost estimated by Oracle for the execution of this step is used to indicate the cost of SQL execution. In theory, the smaller the better (this value may be different from the actual value)

Time (Time): Oracle estimates the time required for the current operation

② opens the execution plan:

Press F5 after executing a select statement in the SQL window to view the execution plan of the query statement that has just been executed

Note: if you use the SQL command in PLSQL to view the execution plan, some SQL*PLUS commands PLSQL cannot support, such as SET AUTOTRACE ON

Third, understand the Oracle implementation plan

3.1 order of execution:

According to the indentation of Operation, the one with the most indentation is executed first; (when the indentation is the same, the top one executes first)

Example: in the figure above, INDEX RANGE SCAN and INDEX UNIQUE SCAN are indented the most, and the top INDEX RANGE SCAN is executed first.

If an action does not have a child ID at the same level, it will be executed first.

Actions at the same level are executed in accordance with the principle of top-most, right-first execution.

For example, in the figure above, if both TABLE ACCESS BY GLOBAL INDEX ROWID and TABLE ACCESS BY INDEX ROWID are indented at the same level, the above TABLE ACCESS BY GLOBAL INDEX ROWID action is executed first; if this action contains a sub-action INDEX RANGE SCAN, then the sub-action INDEX RANGE SCAN on the right is executed first.

The sequence of SQL execution in the figure is as follows:

INDEX RANGE SCAN-> TABLE ACCESS BY GLOBAL INDEX ROWID-> INDEX UNIQUE SCAN-> TABLE ACCESS BY INDEX ROWID-> NESTED LOOPS OUTER-> SORT GROUP BY-> SELECT STATEMENT, GOAL = ALL_ROWS

(note: PLSQL provides a function button to view the order of execution (the red box in the figure above))

3.2 some instructions on the actions in the picture: 1. TABLE ACCESS BY in the above picture. That is, it describes how the table accesses (or Oracle accesses data) when the action is performed; several ways of table access: (not all)

● TABLE ACCESS FULL (full table scan)

● TABLE ACCESS BY ROWID (checked through ROWID's table)

● TABLE ACCESS BY INDEX SCAN (Index scan)

(1) TABLE ACCESS FULL (full table scan):

Oracle reads all the rows in the table and checks whether each row meets the Where restrictions in the SQL statement. When scanning a full table, you can use multi-block reads (that is, reading multiple blocks at a time) to improve throughput.

Use suggestion: full table scan is not recommended for tables with too much data, unless you need to fetch more data, accounting for 5% ~ 10% or more of the total table data.

(2) TABLE ACCESS BY ROWID (accessed through the ROWID table):

First of all, what is ROWID?

ROWID is the pseudo column automatically added by Oracle to the last column of each row in the table. Since it is a pseudo column, it is stated that the value of ROWID is not physically stored in the table.

You can use it like other columns, but not add, delete, or change the value of the column; once a row of data is inserted, the corresponding ROWID is unique in the life cycle of the row, even if the row migration occurs, the Row ID value of the row remains the same.

Let's go back to TABLE ACCESS BY ROWID:

The ROWID of a row indicates the data file, the data block and the location of the row in the block, so you can quickly locate the target data through ROWID, which is also the fastest way to access single-row data in Oracle.

(3) TABLE ACCESS BY INDEX SCAN (index scan):

In the index block, both the key value of each index and the ROWID of the row with that key value are stored.

The possible conceptual structure of an index on a numeric column is shown in the following figure:

So index scanning is actually divided into two steps:

Ⅰ: scan the index to get the corresponding ROWID

Ⅱ: navigate to a specific row to read data through ROWID

-Index scan extension--

There are five types of index scans:

● INDEX UNIQUE SCAN (index unique scan)

● INDEX RANGE SCAN (Index range scan)

● INDEX FULL SCAN (index full scan)

● INDEX FAST FULL SCAN (Index Quick scan)

● INDEX SKIP SCAN (Index Jump scan)

A) INDEX UNIQUE SCAN (index unique scan):

Scans for unique indexes (UNIQUE INDEX) return at most one record at a time

When there are UNIQUE or PRIMARY KEY constraints in a field in a table, Oracle often scans uniqueness.

B) INDEX RANGE SCAN (index range scan):

Use an index to access multiple rows of data

There are three situations where an index range scan occurs:

Range operators (such as: >, =, > = <) are used on unique index columns

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.