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

In the previous article, I wrote about the common execution plans in Oracle. You can refer to the article: http://hbxztc.blog.51cto.com/1587495/1901416, which introduces some other typical execution plans.

1. AND-EQUAL (INDEX MERGE)

AND-EQUAL is also known as INDEX MERGE. As the name implies, INDEX MERGE means that if there are multiple equivalence conditions for different single columns in the where condition, and there are indexes with single key values on these columns, Oracle may scan these indexes separately with the corresponding single equivalence condition. Oracle then merges these rowid collections from scanning a single index, and if the same rowid can be found in these collections, then this rowid is the rowid corresponding to the final execution result of the target SQL. Finally, Oracle only needs to use these rowid to return to the table to get the final execution result of the target SQL.

The corresponding keyword for AND-EQUAL in the execution plan is "AND-EQUAL", and we can use Hint to force Oracle to use AND-EQUAL.

Look at an example:

Zx@MYDB > create table emp_temp as select * from scott.emp;Table created.zx@MYDB > create index idx_mgr on emp_temp (mgr); Index created.zx@MYDB > create index idx_deptno on emp_temp (deptno); Index created.zx@MYDB > select / * + and_equal (emp_temp idx_mgr idx_deptno) * / empno,job from emp_temp where mgr=7902 and deptno=20 EMPNO JOB--7369 CLERK

As you can see from the above display, the execution plan for this SQL is now AND-EQUAL for indexes IDX_MGR and IDX_DEPTNO.

2. INDEX JOIN

INDEX JOIN is easily misleading because it does not refer to table joins that are normally targeted at multiple tables. Here INDEX JOIN refers to joins between different indexes on a single table.

Also take the above EMP_TEMP as an example. Two single-key B*Tree indexes have been created on columns MGR and DEPTNO, respectively. If the SQL statement "select mgr,deptno from emp_temp" is executed at this time, because the columns MGR and DEPTNO to be queried here can be derived from indexes IDX_MGR and IDX_DEPTNO (without considering null values), there is no need to return to the table, so in addition to the regular execution method Oracle can also use the following methods: scan the indexes IDX_MGR and IDX_DEPTNO respectively, and record the result set as result set 1 and result set 2 respectively, and then make a connection between result set 1 and result set 2, the connection condition is "result set 1.rowidth = result set 2.rowid", so the final connection result (without going back to the table) is the implementation result of the above SQL.

Obviously, the execution efficiency of INDEX JOIN for the above SQL is not as efficient as that of building a combined index on columns MGR and DEPTNO, and then scanning the whole set of indexes directly. INDEX JOIN simply provides an optional execution path for CBO, and in most cases it's just an extra option.

Take a look at the example:

Zx@MYDB > delete from emp_temp where mgr is null;1 row deleted.zx@MYDB > commit;Commit complete.zx@MYDB > alter table emp_temp modify mgr not null;Table altered.zx@MYDB > alter table emp_temp modify deptno not null;Table altered.zx@MYDB > select mgr,deptno from emp_temp; MGR DEPTNO--7839 10. 7698 3013 rows selected.

As you can see from the above display, the execution plan for the target SQL is now the HASH JOIN for indexes IDX_MGR and IDX_DEPTNO.

3. VIEW

When Oracle deals with a SQL that contains a view, the corresponding execution plan has the following two forms, depending on whether the view can be used as a view merge (View Merging).

If view merging can be done, then Oracle can directly aim at the base table of the view when executing the SQL. At this time, the keyword "VIEW" will not appear in the execution plan of SQL. (we cannot rely entirely on the appearance of the keyword "VIEW" to determine whether Oracle has done a view merge, because for some SQL, the keyword "VIEW" may still be displayed in the corresponding execution plan even though Oracle has done a view merge).

If the view merge cannot be done, Oracle will treat the view as a whole and execute it independently, and the keyword "VIEW" will appear in the SQL execution plan.

Look at an example and use the EMP_ temp table above:

Zx@MYDB > create view emp_mgr_view as select * from emp_temp where job='MANAGER';View created.zx@MYDB > select empno,sal from emp_mgr_view where ename='CLARK'; EMPNO SAL--7782 2450

As can be seen from the above display, the execution plan of SQL now is a full table scan of the table EMP_TEMP, and the filter query condition of the full table scan is filter ("ENAME" = 'CLARK' AND "JOB" =' MANAGER'). Obviously, here Oracle does the view merge, directly queries the base table EMP_TEMP of the view EMP_MGR_VIEW, and pushes the where conditions for the view to the interior of the view, merging with the constraints of the original view creation.

Now modify the definition of the view EMP_MGR_VIEW by adding the ROWNUM keyword to its creation statement, so that the newly created view EMP_MGR_VIEW with the same name will no longer be able to merge views:

Zx@MYDB > create or replace view emp_mgr_view as select * from emp_temp where job='MANAGER' and rownumselect empno,sal from emp_mgr_view where ename='CLARK'; EMPNO SAL--7782 2450

As can be seen from the above display, the keyword "VIEW" is now included in the execution plan of the SQL, which indicates that the Oracle does not merge the view EMP_MGR_VIEW here, and the view EMP_MGR_VIEW is executed independently by Oracle as a whole.

4. FILTER

FILTER literally translates to filter, filter, it is a special implementation plan, the corresponding implementation process is the following three steps:

Get a driven result set

Filter the records that do not meet the conditions from the above driving result set according to certain filtering conditions.

The remaining records in the result set are returned to the end user or continue to participate in the next execution step.

Look at an example, or use the above view EMP_MGR_VIEW:

Zx@MYDB > select empno,ename from emp where empno in (select empno from emp_mgr_view); EMPNO ENAME- 7566 JONES 7698 BLAKE 7782 CLARK

As you can see from the above display, the execution plan of the SQL is now a nested loop connection, and there is no execution plan of the type of FILTER that we want. This is because Oracle has done a subquery expansion (Subquery Unnexting) here, that is, the subquery is merged with its external SQL and converted into a view VW_NOS_1 and a table EMP to join.

Here, use Hint to disable the subquery and re-execute the above SQL after expansion:

Zx@MYDB > select empno,ename from emp where empno in (select / * + NO_UNNEST * / empno from emp_mgr_view); EMPNO ENAME- 7566 JONES 7698 BLAKE 7782 CLARK

As you can see from the above display, the SQL is now going to be the type of FILTER execution plan we want.

The FILTER-type execution plan is actually an improved nested loop join, unlike the nested loop join, which has to access the driven table as many times as there are records in the driving result set.

Verify with an experiment:

Zx@MYDB > select * from T1; COL1 COL2- 1 A2B 3 Bzx@MYDB > select * from T2 / Col2 COL3-----An A2B B2D D2zx@MYDB > select / * + gather_plan_statistics * / * from T1 where col2 in (select / * + no_unnest * / col2 from T2) COL1 COL2- 1 A 2 B 3 B

Notice that the value of column A-Rows corresponding to the execution step of id=2 in the above display is 3. The value of column Starts corresponding to the execution step of 3 is 2, indicating that although the number of driven result sets obtained by full table scan T1 is 3, the actual number of visits to driven table T2 when following the Filter type execution plan is not 3, but 2. This is because although the number of table T is 3, the number of distinct values of its column COL2 is only 2, so when you filter the data in table T1 with the filter condition "where col2 in (select / * + no_unnest * / col2 from T2)", you only need to access table T2 twice.

5. SORT

SORT means sorting, and the SORT in the execution plan usually appears in the form of combinations, including but not limited to the following:

SORT AGGREGATE

SORT UNIQUE

SORT JOIN

SORT GROUP BY

SORT ORDER BY

BUFFER SORT

Even if the keyword "SORT" appears in the execution plan, it does not necessarily mean that sorting is required. For example, SORT AGGREGATE and BUFFER SORT do not necessarily need sorting.

Look at an example and use the EMP_ temp table above:

Zx@MYDB > set autotrace traceonlyzx@MYDB > select sum (sal) from emp_temp where job='MANAGER'

As you can see from the above display, now the execution plan of SQL is SORT AGGREGATE, and the SQL executed here only asks for an sum. obviously, there is no need to sort here. The values of sort (memroy) and sort (disk) in the statistics are both 0, which also means that Oracle does not do any sorting operation when performing this SQL, so we say that SORT AGGREGATE does not necessarily need sorting, and the keyword "SORT" is somewhat confusing.

Here is another example:

Zx@MYDB > set autotrace offzx@MYDB > select distinct ename from emp_temp where job='MANAGER' order by ename;ENAME--BLAKECLARKJONES

The meaning of the above SQL is to sort and deduplicate, and its corresponding execution plan will be SORT UNIQUE.

Zx@MYDB > select / * + use_merge (T1 / T2) * / T1 empnorecy t1.enamelanguage T1 2.sal from scott.emp T1 where t1.empno=t2.empno empathy temp T2 where t1.empno=t2.empno

As you can see from the above display, the execution plan of the SQL now follows the sorting and merging of EMP and EMP_TEMP. The SORT JOIN type execution plan usually appears in the sort merge join, which is the first step in the execution plan corresponding to the sort merge join.

Then execute the following SQL:

Zx@MYDB > select ename from emp_temp where job='MANAGER' order by ename;ENAME--BLAKECLARKJONES

The above SQL means that only simple sorting is needed, and its corresponding execution plan will be SORT ORDER BY:

Then execute the following SQL:

Select ename from emp_temp where job='MANAGER' group by ename order by ename;ENAME--BLAKECLARKJONES

The above SQL means both sorting and grouping, so its corresponding execution plan will be SORT GROUP BY:

Finally, execute the following SQL:

Select t1.empno,t2.ename from scott.emp t1,emp_temp t2

As you can see from the above display, the execution plan of the SQL now takes a Cartesian join to the table EMP_TEMP and the primary key PK_EMP on the table EMP, because the join condition is not specified in the above SQL. The steps of implementing the plan here are as follows: first, the full table scan table EMP_TEMP, and the scan result is recorded as result set 1; then the index of the primary key PK_EMP on table EMP is quickly scanned, and the scan result load is put into PGA, then the result set 1 and result set 2 are Cartesian connected, and finally the result of Cartesian connection is the final execution result of the above SQL. The keyword "BUFFER SORT" in the execution plan means that Oracle will borrow PGA and put the scan result load in, which has the advantage of saving all kinds of extra overhead caused by the corresponding cache in SGA (such as holding, releasing related Latch, etc.). PGA is often used for sorting, which may be the origin of the keyword SORT in "BUFFER SORT".

It is important to note that BUFFER SORT does not necessarily sort, it may or may not.

The most intuitive way to see whether a SQL is sorted is to look at the values of "sorts (memory)" and "sorts (disk)" in its statistics. If the values of these two metrics are greater than 0, the SQL has experienced sorting at execution time. Unfortunately, these two metrics are not allowed for BUFFER SORT, so we need to use the value of "keys" in the "Column Projection Information" part of the real execution plan of the target SQL to determine whether the corresponding BUFFER SORT is sorted or not. The value of "# keys" indicates the number of actual sequences of the execution step, and if the value of "# keys" is greater than 0, the execution step is indeed ordered.

Look at the following SQL:

Set autotrace traceonlyzx@MYDB > select t1.enamedirection T2. Loc from scott.emp T1 scott.dept T2 56 rows selected.Execution Plan---Plan hash value: 2034389985 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | 56 | 784 | 10 (0) | 00:00:01 | | 1 | MERGE JOIN CARTESIAN | 56 | 784 | 10 (0) | 00:00:01 | | 2 | TABLE ACCESS FULL | DEPT | 4 | 32 | 3 (0) | 00:00:01 | 3 | BUFFER SORT | | 14 | | | 84 | 7 (0) | 00:00:01 | | 4 | TABLE ACCESS FULL | EMP | 14 | 84 | 2 (0) | 00:00:01 |- -- Statistics--- 315 recursive calls 0 db block gets 70 consistent gets 11 physical reads 0 redo size 1831 bytes sent via SQL*Net to client 557 bytes received via SQL*Net from Client 5 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 56 rows processed

Notice that the value of sorts (memory) in the "statistics" section of the above display is 7, but since the execution step "BUFFER SORT" of ID=3 appears in the SQL, this does not necessarily mean that the SQL has experienced sorting at execution time.

Let's take a look at the value of "# keys" corresponding to the execution step "BUFFER SORT" of id=3 in the grave:

Zx@MYDB > select sql_id,sql_text from v$sql where sql_text = 'select t1.enameLegacy t2.loc from scott.emp T1 relegation scott.dept t2' SQL_ID SQL_TEXT-- -3dmxcxk72fwr4 select t1.ename T2.loc from scott.emp T1 scott.dept t2zx@MYDB > select * from table (dbms_xplan.display_cursor ('3dmxcxk72fwr4))

As can be seen from the above display, the value of "# keys" corresponding to the execution step "BUFFER SORT" of Id=3 is 0, indicating that the SQL does not have a sort when executing "BUFFER SORT", and the number of sorts is 0.

This validates the point we mentioned earlier: the values of sorts (memory) and sorts (disk) in the statistics are not allowed for BUFFER SORT, and Oracle may not need to sort when performing BUFFER SORT.

6. UNION/UNION ALL

UNION/UNION ALL means to merge two result sets, and if they appear in the execution plan, it means the same thing.

The difference between UNION and UNION ALL is that UNION ALL simply merges the two result sets without any additional processing, while UNION not only simply merges the two result sets, but also sorts and deduplicates the merged result set, that is, UNION is equivalent to doing UNION ALL first, and then SORT UNIQUE the result set after UNION ALL.

Look at an example:

Select empno,ename from scott.emp union all select empno,ename from emp_temp; EMPNO ENAME--7369 SMITH. 7934 MILLER27 rows selected.

As can be seen from the above display, the execution plan of the SQL now follows the UNION ALL,UNION ALL of the result set scanned from the tables EMP and EMP_TEMP. The corresponding keyword in the execution plan is UNION-ALL. Table EMP has 13 records, table emp _ TEMP has 12 records, and the result set after UNION ALL merge is always 25.

Change UNION ALL to UNION:

Zx@MYDB > select empno,ename from scott.emp union select empno,ename from emp_temp; EMPNO ENAME- 7369 SMITH. 7934 MILLER14 rows selected.

As can be seen from the above display, the execution plan of the SQL now follows the UNION,UNION of the result set of the full table scan of EMP and EMP_TEMP. The corresponding keywords in the execution plan are "UNION-ALL" and "SORT UNIQUE", which means that UNION is equivalent to sorting and deduplicating on the basis of UNION ALL. All the data in table EMP_TEMP comes from table EMP, so here the plural of the result set returned by the UNION operation is the number of rows of table EMP.

7. CONCAT

CONCAT is IN-List extension (IN-List Expansion) or OR extension (OR Expansion). The corresponding keyword of IN-List extension / OR extension in the execution plan is "CONCATENATION". Use Hint to force Oracle to use IN-List extension / OR extension.

Take a look at an example:

Zx@MYDB > select empno,ename from scott.emp where empno in (7654, 7698, 7782); EMPNO ENAME--7654 MARTIN 7698 BLAKE 7782 CLARK

As you can see from the above display, the execution plan of the SQL now takes the IN-List iteration of the table EMP and the primary key index PK_EMP.

Use Hint to force Oracle to walk through the IN-List extension

Zx@MYDB > select / * + USE_CONCAT * / empno,ename from scott.emp where empno in (7654, 7698); EMPNO ENAME- 7654 MARTIN 7698 BLAKE 7782 CLARK

From the content shown above, we can see that Hint is invalid, or we should take the IN-List iteration. Use the following two events to disable the IN-List iteration in the current Session, and set the value of the input parameter no_invalidate to false to re-collect the statistics, so that the execution plan of the previous IN-List iteration will not be lost when the above SQL is executed again:

Zx@MYDB > alter session set events' 10142 trace name context forever';Session altered.zx@MYDB > alter session set events' 10157 trace name context forever';Session altered.zx@MYDB > exec dbms_stats.gather_table_stats (ownname= > 'SCOTT',tabname= >' EMP',cascade= > true,method_opt= > 'for all columns size 1 creative remark noisy invalidates = > false); PL/SQL procedure successfully completed.zx@MYDB > select / * + USE_CONCAT * / empno,ename from scott.emp where empno in (76988 USE_CONCAT 7782) EMPNO ENAME--7654 MARTIN 7698 BLAKE 7782 CLARK

As you can see from the above display, the execution plan of the SQL has now become the IN-List extension we want, and the corresponding keyword in the execution plan is CONCATENATION. Here, the meaning of CONCATENATION is equivalent to UNION ALL, that is, the above SQL is equivalent to rewriting UNION ALL into the following form:

Select empno,ename from emp where empno=7782

Union all

Select empno,ename from emp where empno=7698

Union all

Select empno,ename from emp where empno=7654

8. CONNECT BY

CONNECT BY is the keyword corresponding to the hierarchical query (Hierachical Queries) in the Oracle database, and it means the same thing if it appears in execution.

Take a look at an example:

Zx@MYDB > select empno,ename,job,mgr from scott.emp EMPNO ENAME JOB MGR--7369 SMITH CLERK 7902 7499 ALLEN SALESMAN 7698 7521 WARD SALESMAN 7698 7566 JONES MANAGER 7839 7654 MARTIN SALESMAN 7698 7698 BLAKE MANAGER 7839 7782 CLARK MANAGER 7839 7788 SCOTT ANALYST 7566 7839 KING PRESIDENT 7844 TURNER SALESMAN 7698 7876 ADAMS CLERK 7788 7900 JAMES CLERK 7698 7902 FORD ANALYST 7566 7934 MILLER CLERK 7782

From the above, you can see that KING is PRESIDENT, and the value of the MGR it records is NULL, indicating that KING has no superiors.

We execute the following SQL, starting with the record where the KING is located, and divide all the people into different categories according to the relationship between the superiors and subordinates:

Zx@MYDB > select empno,ename,mgr from emp start with empno=7839 connect by prior empno=mgr EMPNO ENAME MGR--7839 KING 7566 JONES 7839 7788 SCOTT 7566 7876 ADAMS 7788 7902 FORD 7566 7369 SMITH 7902 7698 BLAKE 7839 7499 ALLEN 7698 7521 WARD 7698 7654 MARTIN 7698 7844 TURNER 7698 7900 JAMES 7698 7782 CLARK 7839 7934 MILLER 7782

View the execution plan:

From the above display, we can see that the execution plan of the SQL now goes to CONNECT BY, and we can also see the CONNECT BY keyword in the execution plan.

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.