Shulou(Shulou.com)05/31 Report--

The main content of this article is to explain "what is the method of oracle implementation plan", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the method of implementing the plan in oracle"?

First look to the right from the beginning until you see the juxtaposition on the rightmost. For those that are not juxtaposed, execute first on the right: for juxtaposition, those on the top execute first.

That is, juxtaposed indented blocks, executed from top to bottom, non-juxtaposed indented blocks, executed from bottom to top.

The following is an example:

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 NESTED LOOPS

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_HOST_BATCH_DTL'

3 2 INDEX (RANGE SCAN) OF 'XIE2TB_HOST_BATCH_DTL' (NON-UNIQUE)

4 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_BATCH_TRAN_CTRL'

5 4 INDEX (UNIQUE SCAN) OF 'SYS_C0034652' (UNIQUE)

You might as well assume that the rightmost data is the line number, and the statement execution is carried out in blocks.

Look to the right from the beginning of the above execution plan until you find the one juxtaposed on the far right. That is, start looking to the right from SELECT STATEMENT.

Find the first-level indent block, there is only one, there is no priority to speak of, as follows:

1 0 NESTED LOOPS

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_HOST_BATCH_DTL'

3 2 INDEX (RANGE SCAN) OF 'XIE2TB_HOST_BATCH_DTL' (NON-UNIQUE)

4 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_BATCH_TRAN_CTRL'

5 4 INDEX (UNIQUE SCAN) OF 'SYS_C0034652' (UNIQUE)

Then find the second-level indent block in the first-level indent block, and find two, as follows:

The first secondary indent block:

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_HOST_BATCH_DTL'

3 2 INDEX (RANGE SCAN) OF 'XIE2TB_HOST_BATCH_DTL' (NON-UNIQUE)

Second secondary indent block:

4 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_BATCH_TRAN_CTRL'

5 4 INDEX (UNIQUE SCAN) OF 'SYS_C0034652' (UNIQUE)

According to the principle that parallel blocks are executed from top to bottom, all statements of the first second-level indented block will take precedence over those of the second second-level indented block.

Only after the execution of the two second-level indentation blocks can the first-level indentation blocks be executed, because the non-juxtaposed indentation blocks are executed from the bottom up.

Then find the third-level indent block in the second-level indent block.

There is only one tertiary indent block in the first secondary indent block, as follows:

3 2 INDEX (RANGE SCAN) OF 'XIE2TB_HOST_BATCH_DTL' (NON-UNIQUE)

Since there is no indent block at a lower level, the block is executed first. Then execute the secondary indent block.

There is also only one tertiary indent block in the second secondary indent block, as follows:

5 4 INDEX (UNIQUE SCAN) OF 'SYS_C0034652' (UNIQUE)

There are no lower-level indented blocks in the secondary indented block, so after all the statements of the first secondary indented block are executed, the third-level indented block is executed, and then the corresponding secondary indented block is executed.

After the execution of the two second-level indent blocks is completed, the entire first-level indent block is executed.

Therefore, if you sort by row, the order in which the statements are executed is: 3-> 2-> 5-> 4-> 1, that is:

3 2 INDEX (RANGE SCAN) OF 'XIE2TB_HOST_BATCH_DTL' (NON-UNIQUE)->

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_HOST_BATCH_DTL'->

5 4 INDEX (UNIQUE SCAN) OF 'SYS_C0034652' (UNIQUE)->

4 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_BATCH_TRAN_CTRL'->

1 0 NESTED LOOPS

The theoretical explanation of the sequence of the execution plan:

The execution plan is displayed according to certain rules, as follows:

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 NESTED LOOPS

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_HOST_BATCH_DTL'

3 2 INDEX (RANGE SCAN) OF 'XIE2TB_HOST_BATCH_DTL' (NON-UNIQUE)

4 1 TABLE ACCESS (BY INDEX ROWID) OF 'TB_BATCH_TRAN_CTRL'

5 4 INDEX (UNIQUE SCAN) OF 'SYS_C0034652' (UNIQUE)

The first column is the ID of the step (equivalent to what we call the line number)

The second column is the ID of the parent step

The third column is the action to be performed by this step.

During execution, the parent step depends on the child step. The parent step can be executed only after all the corresponding child steps have been executed.

Therefore, the substep of step 0 is 1, the substep of step 1 is 2p4, the substep of step 2 is 3, the substep of step 4 is 5, and there are no substeps in step 3.

Therefore, the module in step 3, or the module in step 5, is executed first. However, step 3 is in front of step 5, so the module where step 3 is located is executed first. Therefore, the order is still:

3-> 2-> 5-> 4-> 1

At this point, I believe you have a deeper understanding of "what is the method of oracle implementation plan". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.