1. Exploration of Execution Plan (1)

/*************************************** **********

Topic: Execution Plan exploration (1)

 

Understand the execution plan and the basics

**************************************** *********/

========================================================== ==================================

Create Table test6

(

ID number (1) not null,

Name varchar2 (10)

)

 

ID name

------------

0 0

1 1

2 2

3

4 B

5

7

--

Create Table test7

(

ID number (1 ),

Name varchar (10 ),

Age number (2)

);

 

Insert into test7 (ID, name)

Select * From test6 where ID <4;

 

========================================================== ==================================1.Selection of index Columns

By comparing the number of unique keys in a column and the number of rows in the table, you can determine the selectivity of the column.

If the ratio of the column's "number of unique keys/number of rows in the table" is close to 1, the higher the selectivity of the column, the more suitable the column will be to create an index, the same index is more selective.

 

Select count (distinct ID) from test6; -- the result is 1, which is the expected column.

Select count (distinct name)/count (1) From test6; -- result 0.8

2.How to view execution plans

(1) explain Plan window using PLSQL

Enter the SQL statement and press F8 to execute it.

Alternatively, in the SQL window, select the code segment and press F5.

(2) Set autotrace

Serial number	Command	Explanation
1	Set autotrace off	This is the default value, that is, disable autotrace.
2	Set autotrace on explain	Show only execution plans
3	Set autotrace on Statistics	Only statistics of execution are displayed.
4	Set autotrace on	Contains 2 or 3 items
5	Set autotrace traceonly	Similar to on, but does not display statement execution results

 

-- After testing, it seems that it can only be executed in SQL plus, and the error cannot set autotrace must be reported in PLSQL.

An error is also returned when sysdba is used for connection.

 

 

(3) use SQL query

SQL> explain Plan for select count (distinct ID)/count (1) From test6;

 

Explained

 

SQL> select plan_table_output from table (dbms_xplan.display ('Plan _ table '));

-- Or select * from table (dbms_xplan.display );

Plan_table_output

---------------------------------------------------------------------

Plan hash value: 2141808149

---------------------------------------------------------------------

| ID | operation | Name | rows | bytes | cost (% CPU) | time

---------------------------------------------------------------------

| 0 | SELECT statement || 1 | 26 | 3 (34) | 00:00:01

| 1 | sort aggregate | 1 | 26 |

| 2 | View | vw_dag_0 | 7 | 182 | 3 (34) | 00:00:01

| 3 | hash group by || 7 | 91 | 3 (34) | 00:00:01

| 4 | table access full | test6 | 7 | 91 | 2 (0) | 00:00:01

---------------------------------------------------------------------

Note

-----

-Dynamic sampling used for this statement (Level = 2)

 

15 rows selected

 

(4) set timing on can return the execution time

 

-- Below isOracleData access methods

3.Full table scans (FTS)

(1) When the query result occupies a high proportion of the total table data, it is faster than taking the index;

(2) You want to scan the entire table (such as a full table and a table index ).

 

3.Table access by rowid or rowid Lookup)

Using rowid to access data can be quickly located on the target data, which is the fastest way for Oracle to access a single row of data.

 

5.Index Scanning

Index scanning can be composed of two steps:

(1) scan the index to obtain the corresponding rowid value.
(2) read the specific data from the table by finding the rowid.

Use Cases:

(1) fewer rows returned (preferably less than 5% );

(2) The returned field contains the index field.

-- Of course, the premise is that the table has to be indexed, if there is no index (* ^__ ^ *)

Create index idx_test6 on test6 (ID );

 

 

6.Unique index Scan)

Searching for a value through a unique index often returns a single rowid. If the unique or primary key constraint exists (it ensures that the statement only accesses a single row), Oracle often implements a unique scan.

Create unique index idx_test6 on test6 (ID );

7.Index range scan)

A single index is used to access multiple rows of data. A typical use of index range scanning on a unique index is a predicate (where condition) range operators (such as >,<, >,>=, <=, and between) are used)

Three cases of using index rang scan:
(A) Use the range operator on the unique index column (><>>=<=)
(B) on the composite index, only some columns are used for query, resulting in multiple rows being queried.
(C) any queries on non-unique index columns.

8.Full index Scan)

 

 

9.Index fast full scan)

Scanning all data blocks in an index is similar to full scan, but it does not sort the queried data, that is, the data is not returned in the order of sorting. In this access method, you can use the multi-block READ function or parallel read to obtain the maximum throughput and shorten the execution time.

 

10.Sort-merge join (sort merge join, smj)

 

1) first generate the data required by test5, and then associate the data with columns (such as. (2) generate the data required by test6, and associate the data with columns (such as B. (3) The last two sorted rows are put together for the merge operation, that is, the two row sources are connected according to the connection conditions.

11.Nested loop (nested loops, NL)

Because test5 has a small amount of data, it is used as the driver table.

In the nested loops connection, Oracle reads each row in test5, and then checks whether there are matched rows in test6. All matched rows are placed in the result set, then process the next row in test5. This process continues until all rows in test5 are processed. This is one of the fastest ways to get the first matching row from the connection operation. This type of connection can be used in statements that require rapid response, with the response speed as the main target.

12.Hash join (HJ)

 

 

13.Cartesian Product)

You don't have to say anything about this name.