11g对Nested Loop Joins做的改动
来源:互联网 发布:淘宝手机商城 编辑:程序博客网 时间:2024/04/29 14:07
最近看网友sql调优时,发现Nested Loop Joins 的执行计划和原来的不一样了,不明白是咋回事,查看了11g的doc才知道,11g对Nested Loop Joins做了改动。
粘一下:
11.3.3.1 Original and New Implementation for Nested Loop Joins
Oracle Database 11g introduces a new implementation for nested loop joins. As a result, execution plans that include nested loops might appear different than they did in previous releases of Oracle Database. Both the new implementation and the original implementation for nested loop joins are possible in Oracle Database 11g. So, when analyzing execution plans, it is important to understand that the number of NESTED LOOPS join row sources might be different.
11.3.3.1.1 Original Implementation for Nested Loop Joins
Consider the following query:
SELECT e.first_name, e.last_name, e.salary, d.department_name
FROM hr.employees e, hr.departments d
WHERE d.department_name IN ('Marketing', 'Sales')
AND e.department_id = d.department_id;
Before Oracle Database 11g, the execution plan for this query might appear similar to the following execution plan:
In this example, the outer side of the join consists of a scan of the hr.departments table that returns the rows that match the condition department_name IN ('Marketing', 'Sales'). The inner loop retrieves the employees in the hr.employees table that are associated with those departments.
11.3.3.1.2 New Implementation for Nested Loop Joins
Oracle Database 11g introduces a new implementation for nested loop joins to reduce overall latency for physical I/O. When an index or a table block is not in the buffer cache and is needed to process the join, a physical I/O is required. Oracle Database 11g can batch multiple physical I/O requests and process them using a vector I/O instead of processing them one at a time.
As part of the new implementation for nested loop joins, two NESTED LOOPS join row sources might appear in the execution plan where only one would have appeared in prior releases. In such cases, Oracle Database allocates one NESTED LOOPS join row source to join the values from the table on the outer side of the join with the index on the inner side. A second row source is allocated to join the result of the first join, which includes the rowids stored in the index, with the table on the inner side of the join.
Consider the query in "Original Implementation for Nested Loop Joins". In Oracle Database 11g, with the new implementation for nested loop joins, the execution plan for this query might appear similar to the following execution plan:
In this case, the rows from the hr.departments table constitute the outer side of the first join. The inner side of the first join is the index emp_department_ix. The results of the first join constitute the outer side of the second join, which has the hr.employees table as its inner side.
There are cases where a second join row source is not allocated, and the execution plan looks the same as it did in prior releases. The following list describes such cases:
1.All of the columns needed from the inner side of the join are present in the index, and there is no table access required. In this case, Oracle Database allocates only one join row source.
2.The order of the rows returned might be different than it was in previous releases. Hence, when Oracle Database tries to preserve a specific ordering of the rows, for example to eliminate the need for an ORDER BY sort, Oracle Database might use the original implementation for nested loop joins.
3.The OPTIMIZER_FEATURES_ENABLE initialization parameter is set to a release before Oracle Database 11g. In this case, Oracle Database uses the original implementation for nested loop joins.
没有11g的环境,以后可以注意一下。最近看了一些sql调优化的例子,总结出嵌套循环的使用情况:
通常情况下驱动表(行源) 应该返回少量数据(占源表的6%,8%,10%),而且被驱动表上面有选择性很高的索引时,CBO才选择嵌套循环。
粘一下:
11.3.3.1 Original and New Implementation for Nested Loop Joins
Oracle Database 11g introduces a new implementation for nested loop joins. As a result, execution plans that include nested loops might appear different than they did in previous releases of Oracle Database. Both the new implementation and the original implementation for nested loop joins are possible in Oracle Database 11g. So, when analyzing execution plans, it is important to understand that the number of NESTED LOOPS join row sources might be different.
11.3.3.1.1 Original Implementation for Nested Loop Joins
Consider the following query:
SELECT e.first_name, e.last_name, e.salary, d.department_name
FROM hr.employees e, hr.departments d
WHERE d.department_name IN ('Marketing', 'Sales')
AND e.department_id = d.department_id;
Before Oracle Database 11g, the execution plan for this query might appear similar to the following execution plan:
-------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 19 | 722 | 3 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 220 | 1 (0)| 00:00:01 || 2 | NESTED LOOPS | | 19 | 722 | 3 (0)| 00:00:01 ||* 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 | 2 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales') 4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
In this example, the outer side of the join consists of a scan of the hr.departments table that returns the rows that match the condition department_name IN ('Marketing', 'Sales'). The inner loop retrieves the employees in the hr.employees table that are associated with those departments.
11.3.3.1.2 New Implementation for Nested Loop Joins
Oracle Database 11g introduces a new implementation for nested loop joins to reduce overall latency for physical I/O. When an index or a table block is not in the buffer cache and is needed to process the join, a physical I/O is required. Oracle Database 11g can batch multiple physical I/O requests and process them using a vector I/O instead of processing them one at a time.
As part of the new implementation for nested loop joins, two NESTED LOOPS join row sources might appear in the execution plan where only one would have appeared in prior releases. In such cases, Oracle Database allocates one NESTED LOOPS join row source to join the values from the table on the outer side of the join with the index on the inner side. A second row source is allocated to join the result of the first join, which includes the rowids stored in the index, with the table on the inner side of the join.
Consider the query in "Original Implementation for Nested Loop Joins". In Oracle Database 11g, with the new implementation for nested loop joins, the execution plan for this query might appear similar to the following execution plan:
------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |-------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 19 | 722 | 3 (0)| 00:00:01 || 1 | NESTED LOOPS | | | | | || 2 | NESTED LOOPS | | 19 | 722 | 3 (0)| 00:00:01 ||* 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 | 2 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 220 | 1 (0)| 00:00:01 |------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales') 4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
In this case, the rows from the hr.departments table constitute the outer side of the first join. The inner side of the first join is the index emp_department_ix. The results of the first join constitute the outer side of the second join, which has the hr.employees table as its inner side.
There are cases where a second join row source is not allocated, and the execution plan looks the same as it did in prior releases. The following list describes such cases:
1.All of the columns needed from the inner side of the join are present in the index, and there is no table access required. In this case, Oracle Database allocates only one join row source.
2.The order of the rows returned might be different than it was in previous releases. Hence, when Oracle Database tries to preserve a specific ordering of the rows, for example to eliminate the need for an ORDER BY sort, Oracle Database might use the original implementation for nested loop joins.
3.The OPTIMIZER_FEATURES_ENABLE initialization parameter is set to a release before Oracle Database 11g. In this case, Oracle Database uses the original implementation for nested loop joins.
没有11g的环境,以后可以注意一下。最近看了一些sql调优化的例子,总结出嵌套循环的使用情况:
通常情况下驱动表(行源) 应该返回少量数据(占源表的6%,8%,10%),而且被驱动表上面有选择性很高的索引时,CBO才选择嵌套循环。
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/optimops.htm#PFGRF94636
- 11g对Nested Loop Joins做的改动
- SQL Server 执行连接的方式 - Nested Loops Joins
- XP SP2对raw socket所做的改动
- Android对Linux内核做的改动汇总
- Google对Kernel做了哪些改动?
- nested loop 原理
- Nested-Loop Join Algorithms
- NESTED LOOPS & HASH JOINS & MERGE JOIN
- oracle的表连接hash join、nested loop join
- 关于多层 nested loop的估值问题
- 安装linux需要做的改动
- 031.Nested Loop 嵌套循环
- 031.Nested Loop 嵌套循环
- oracle sql 优化--nested loop
- mysql Index Nested-Loop Join
- TMS2.0中对DBUtil的改动
- JSP 2.1对JSP2.0的改动
- Android对Kernel的改动汇总
- GDAL源码剖析(二)之编译说明
- array_merge() [function.array-merge]: Argument #1 is not an array in ……错误的解决办法
- 三种方式解析XML(DOM,Pull,Sax,并带文件编码识别).........................by 私念
- 告诉你,如何成就DBA职业生涯
- Ubuntu 构建Linux 嵌入式开发环境
- 11g对Nested Loop Joins做的改动
- 单链表
- 到底是什么偷走了我们的快乐
- java遍历map所有元素
- cin.clear()与cin.sync()与cin.ignore()
- Android获取图片大小以及获取指定大小的缩略图
- kruscal求最小生成树
- liunx ctrl+d 与windows的ctrl+z
- vs中怎么显示行数