Oracle execution plan

Let's talk about the Oracle execution plan in detail today. The so-called execution plan is the execution plan made before a certain SQL statement is executed, or the execution path. There are two types of Oracle Optimizer models: RBO: Rule Based Optimizer and Cost-Based Optimizer ):

It can be subdivided:

CHOOSE, RULE, FIRST_ROWS, ALL_ROWS

In the CHOOSE mode, if the queried table has statistical information, it is executed based on the cost (FIRST_ROWS). Otherwise, the RULE-based method is used to execute this SQL statement, that is, RULE. (PS: RBO has been abolished in 10 Gb)

First, I want to understand several concepts: (Glossary come from the Internet and my own understanding)

Row source: returns a set of qualified rows, multi-Table associations, or row data returned by a single table query.

Predicate: To put it bluntly, it is the where condition in the query.

Driving Table, also known as the outer Table, is used in nested tables and hash connections. It generally refers to tables with fewer row sources returned after predicate filter.

Probed Table: compared with the previous concept, it is called an inner Table. Generally, it is a Table that returns multiple row sources.

Concatenated index: an index composed of multiple columns. A pilot column index is generally required to take effect. For example, the indexes in the emp table include create index... on emp (col1, col2, col3 );

The pilot column is col1. To use the index, it must contain the field col1, for example, where col1 = .. or where col1 = .. and col2 =... where col2 = .. then the index is not taken away.

Several data access methods:

1. full Table Scan: to read all rows in a Table, you can set the db_block_multiblock_read_count parameter of the database to improve the efficiency of Full Table Scan, if the queried data exceeds 5%-10% of the table, you can use full table scan.

2. rowid (Table Access by ROWID)

3. Index Scan: the Index has been sorted. Therefore, if the value queried by the Index is also sorted, you do not need to sort the Index again, which improves the efficiency. Index scanning is also divided into: index unique scan, index range scan, index full scan, index fast full scan

Table connections often involve the following types:

Nested loop join (the outer table is used as the drive, and each row has efficient access to the inner table. Therefore, the row source of the driving table is usually small, and the inner table is large, but it can be accessed efficiently through the index)

Sort-merge join (sort the two associated row sources by the connection column first, and then connect. This method is a waste of resources because it requires sorting. However, if the row source to be connected has already been sorted, this method is highly efficient. Used for non-equi join. The associated columns are indexed ,)

Hash connections (two large row sources can be used for equivalent connections only)