了解oracle Nestd loop join

最新推荐文章于 2024-09-03 09:58:42 发布

原创最新推荐文章于 2024-09-03 09:58:42 发布 · 713 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #优化

sql tunning 专栏收录该内容

9 篇文章

订阅专栏

本文介绍了嵌套循环连接的工作原理及应用场景，包括其在Oracle 11g R2中的新特性，通过示例展示了优化器如何选择外部表和内部表，并解释了优化器使用嵌套循环连接的条件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--嵌套循环连接的使用情景

Nested loop joins are useful when the following conditions are true:

■ The database joins small subsets of data. ---与比较小的结果集进行连接

■ The join condition is an efficient method of accessing the second table.--连接条件有利于高效的访问第二张表

--嵌套循环连接涉及到的步骤

1. The optimizer determines the driving table and designates it as the outer table.
2. The other table is designated as the inner table.
3. For every row in the outer table, Oracle Da tabase accesses all the rows in the inner
table. The outer loop is for every row in the outer table and the inner loop is for
every row in the inner table. The outer loop appears before the inner loop in the
execution plan, as follows:
NESTED LOOPS
outer_loop
inner_loop

1、优化器决定驱动表并作为外部表（outer table）

2、其他表作为内部表（inner table）

3、当外部表每执行一次循环后，会在内部表查找符合连接条件的行

oracle 11gR2 嵌套循环的新特性

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.

---当索引或者数据块不在buffer cache中oracle会使用批处理的方式处理多个物理I/O的请求

---使用 vector i/o 。避免一次一个地处理它们

scott@ORCL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

hr@ORCL> set autot traceonly
hr@ORCL> SELECT e.first_name, e.last_name, e.salary, d.department_name
2 FROM hr.employees e, hr.departments d
3 WHERE d.department_name IN ('Marketing', 'Sales')
4 AND e.department_id = d.department_id;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 722 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 19 | 722 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 | 3 (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")