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

This article mainly explains "how to check the execution order of the sql implementation plan in oracle". The explanation in the article is simple and clear, easy to learn and understand. Please follow the editor's train of thought to study and learn "how to check the execution order of the sql implementation plan in oracle".

View the execution order of the sql execution plan:

First, keep looking to the right from the beginning until you see the juxtaposition on the far right. If you see the juxtaposition, you will execute it first. If you see the juxtaposition, you will look at it from top to bottom. For the juxtaposition part, the above will be executed first.

Common execution plans in oracle

I. the common implementation plan of the table

1 、 table access full

2 、 table access by user rowid

3 、 table access by index rowid

II. Implementation plan related to B tree index

1 、 index unique scan

2 、 index rang scan

3 、 index full scan

4 、 index skip scan

5 、 index fast full scan

III. Execution plan related to bitmap index

Bitmap index (physical storage structure is: indexed key value, corresponding to the upper limit of rowid, corresponding to the lower limit of rowid, bitmap segment (bitmap segment))

Bitmap segment: the maximum is only 1max 2 of the bitmap index leaf block size.

The physical storage structure of the bitmap index determines that the lock granularity of the bitmap index is on the bitmap segment, so the bitmap index has no row lock, and to lock is to lock the bitmap segment, and multiple data rows may correspond to the same bitmap segment, so the bitmap index is not suitable for high concurrency and frequently modified OLTP, which will lead to serious concurrency problems, even deadlocks.

Several advantages of bitmap index over btree

1. Bitmap segments are compressed and stored, so when the index column has less distinct, the bitmap index will obviously save storage space.

2. Indexes need to be built on multiple columns, and bitmap indexes will significantly reduce space.

3. Bitmap can quickly realize some operations of and and or, because bitmap index can realize fast bitwise operation.

Relevant implementation plan

1. Bitmap index single key value scan bitmap index single value

2 、 bitmap index rang scan

3 、 bitmap index full scan

4 、 bitmap index fast full scan

5 、 bitmap and

6 、 bitmap or

7 、 bitmap minus

After using the bitmap index, the result will be converted to rowid

Bitmap conversion to rowids

IV. Execution plans related to table joins

Hash join

Nested loops

Merge join sort join

Hash join anti

Nested loops anti

Merge join anti sort join

Hash join semi

Nested loops semi

Merge join semi sort join

5. Other typical implementation plans

And equal (index merge)

Index join

View

Filter an improved nested loop join, unlike a nested loop join, in which the driven table is accessed as many times as there are records in the drive result set.

Sort is divided into the following categories. If sort appears in the execution plan, it does not necessarily mean that it must be sorted. For example, sort aggregate and buffer sort may not be sorted.

Sort aggregate

Sort unique

Sort join

Sort group by

Sort order by

Buffer sort (sort (memory) and sort (disk) in statistics are not necessarily accurate for buffer sort, even if there is a numerical value, they may or may not be sorted), which need to be sorted through events like 10032, or select * from table (dbms_xplan.display_cursor ('ggtpk4y6089ma',0,'advanced')); to check the specific information to determine whether it is sorted.

Union/union all

Union all only merges the result set, and union is equivalent to union all first and then sort unique the result set

Concat

Connect by

Select empno,ename,mgr from emp start with empno=7839 connect by prior empno=mgr

VI. Stability of execution plan in ORACLE

1. Use sql profile to stably execute the plan dbms_sqltune

Two types of automatic and manual

Automatic cannot lock the execution plan, it needs to be combined with the original statistics of sql, and can only adjust the execution plan.

Manual can steadily execute the plan, which automatic does not have.

2. SPM (SQL PLAN MANAGEMENT)

New features only available in 11G

Thank you for reading, the above is the content of "how to view the execution order of the sql implementation plan in oracle". After the study of this article, I believe you have a deeper understanding of how to view the execution order of the sql implementation plan in oracle, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.