酷酷娱乐网

站内广告

Oracle Joins

时间:2020-09-03 04:12:02   作者:酷酷娱乐网   来源:www.kukuyl.com  
内容摘要:



Oracle® Database



SQL Tuning Guide


Part IV



SQL Operators: Acc......

Oracle® Database   SQL Tuning Guide

Part IV   SQL Operators: Access Paths and Joins

--- 8 Optimizer Access Paths

--- 9 Joins

9   Joins

Oracle 数据库为连接行集提供了几种优化。

9.1 关于连接

连接 将来自两个行源(如表或视图)的输出合并起来,并返回一个行源。返回的行源是数据集。

连接的特征是在WHERE (非 ANSI )或 FROM 中多个表的. JOIN ANSI 语。当FROM 子句中存在多个表时, Oracle 数据库将执行 接。

接条件使用表达式比较两个行源。 连接 条件定义表之间的关系。如果语句未指定 接条件,则数据库执行笛卡尔 (Cartesian join) ,将一个表中的每一行与另一个表中的每一行匹配。

9.1.1   连接

通常, 接树表示为 树结构。

如下图所示,table1 为左表, table2 为右表。优化器从左到右处理连接。例如,如果这个图形描述了一个嵌套循环连接 (nested loops join,) ,那么table1 是外部循环 (outer loop) table2 是内部循环 (inner loop)

联接的输入可以是前一个联接的结果集。如果联接树的每个内部节点的右子节点是一个表,那么该树就是一个左深联接树,如下面的示例所示。大多数连接树都是左深连接。

如果联接树的每个内部节点的左子节点是一个表,则该树称为右深联接树,如下图所示。

如果联接树的内部节点的左子节点或右子节点可以是联接节点,则该树称为浓密联接树。在下面的示例中,table4 是一个联接节点的右子节点, table1 是一个联接节点的左子节点, table2 是一个联接节点的左子节点。

在另一个变体中,联接的两个输入都是前一个联接的结果

9.1.2 优化器如何执行连接语句

数据库连接行源对。当FROM 子句中存在多个表时,优化器必须确定每对表中哪一个联接操作最有效。

优化器必须做出下表所示的相关决策。

Table 9-1 Join Operations

操作:访问路径

解释:对于简单语句,优化器必须选择一个访问路径来从连接语句中的每个表检索数据。例如,优化器可能会在全表扫描或索引扫描之间进行选择。

操作:连接方式

解释:要联接每一对行源,Oracle 数据库必须决定如何联接。“ how ”是连接方法。可能的连接方法有嵌套循环 (nested loop) 、排序合并 (sort merge) 和哈希连接 ( hash joins) 。笛卡尔连接 (Cartesian join) 需要前面的连接方法之一。每种连接方法都有特定的情况,在这些情况下,它比其他方法更适合。

操作:连接类型

解释:联接条件确定联接类型。例如,内部联接仅检索与联接条件匹配的行。外部联接检索与联接条件不匹配的行。

操作:连接顺序

解释:要执行连接两个以上表的语句,Oracle 数据库先连接两个表,然后将产生的行源连接到下一个表。这个过程将一直持续下去,直到所有表都连接到结果中为止。例如,数据库连接两个表,然后将结果连接到第三个表,然后将这个结果连接到第四个表,依此类推。

9.1.3 优化器如何为连接选择执行计划

在确定连接顺序和方法时,优化器的目标是尽早减少行数,以便在整个SQL 语句执行过程中执行更少的工作。

优化器根据可能的连接顺序、连接方法和可用的访问路径生成一组执行计划。然后,优化器估计每个计划的成本,并选择成本最低的一个。在选择执行计划时,优化器会考虑以下因素:

优化器首先确定连接两个或多个表是否会导致一个至多包含一行的行源。

优化器根据表上惟一的主键约束来识别这种情况。如果存在这种情况,那么优化器将首先按照连接顺序放置这些表。然后优化器优化其余表集的连接。

对于带有外部连接条件的连接语句,带有外部连接操作符的表通常按照连接顺序位于条件中的另一个表之后。

通常,优化器不会考虑违反此准则的连接顺序,尽管在某些情况下,优化器会覆盖此顺序条件。类似地,当一个子查询被转换成反连接或半连接时,子查询中的表必须位于它们所连接或关联的外部查询块中的那些表之后。然而, 哈希 反连接和半连接能够在某些情况下覆盖这个排序条件。

优化器通过计算估计的I/Os CPU 来估计查询计划的成本。这些 I/Os 具有与之相关的特定成本 : 单个块 I/O 的成本,以及多个块 I/Os 的成本。另外,不同的函数和表达式都有与之相关的 CPU 成本。优化器使用这些指标确定查询计划的总成本。这些指标可能会受到许多初始化参数和编译时的会话设置的影响,比如 DB_FILE_MULTI_BLOCK_READ_COUNT 设置、系统统计信息等等。

例如,优化估计成本的方式如下:

嵌套循环联接的成本取决于将外部表的每个选定行及其内部表的每个匹配行读入内存的成本。优化器使用数据字典中的统计信息来估计这些成本。

排序合并连接的成本很大程度上取决于将所有源读入内存并进行排序的成本。

哈希连接 的成本很大程度上取决于在连接的一个输入端构建 哈希 表,并使用连接另一端的行探测它的成本。

9-1 估计连接顺序和方法的成本

从概念上讲,优化器构造了一个连接顺序和方法的矩阵,以及与每个连接顺序和方法相关的成本。例如,优化器必须确定如何在查询中最好地联接date_dim lineorder 表。下表显示了方法和订单的可能变化,以及每种方法和订单的成本。在本例中,嵌套循环联接的顺序是 date_dim, lineorder 的成本最低。

9-2 date_dim lineorder 表连接的示例成本

9.2 连接 方式

连接 方式 是连接两个行源的机制。

根据统计数据,优化器选择估计成本最低的方法。如图9-5 所示,每个连接方法有两个子方法:驱动(也称为外部)行源和 驱动(也称为内部)行源。

9.2.1   嵌套循环连接 (Nested Loops Joins)

嵌套循环将外部数据集连接到内部数据集。

对于与单表谓词匹配的外部数据集中的每一行,数据库检索内部数据集中满足连接谓词的所有行。如果索引可用,那么数据库可以使用它来访问rowid 的内部数据集。

9.2.1.1 优化器 何时 考虑嵌套循环连接

当数据库连接小的数据子集时,嵌套循环连接非常有用; 当数据库连接大的数据集时,优化器模式设置为 FIRST_ROWS ,或者连接条件是访问内部表的有效方法时,嵌套循环连接非常有用。

注意:

连接所期望的行数是驱动优化器决策的因素,而不是底层表的大小。例如,一个查询可能连接两个各有10 亿行的表,但是由于过滤器的原因,优化器期望每个数据集有 5 行。

一般来说,嵌套循环联接在具有联接条件索引的小表上最有效。如果行源只有一行,如对主键值进行相等查找(例如,employee_id=101 ),则联接是一个简单的查找。优化器总是试图将最小的行源放在第一位,使其成为驱动表。

优化器决定使用嵌套循环的因素有很多。例如,数据库可以在一个批处理中从外部行源读取几行。根据检索到的行数,优化器可以选择到内部行源的嵌套循环或哈希联接。例如,如果查询将部门联接到驱动表employees ,并且谓词在 employees.last_name 中指定了一个值,则数据库可能会读取 last_name 索引中的足够条目,以确定是否传递了内部阈值。如果阈值未通过,优化器将选择到部门的嵌套循环联接,如果阈值通过,则数据库将执行哈希联接,这意味着读取其余员工,将其哈希到内存中,然后加入到部门。

如果内部循环的访问路径不依赖于外部循环,则结果可以是笛卡尔积:对于外部循环的每次迭代,内部循环生成相同的行集。若要避免此问题,请使用其他联接方法联接两个独立的行源。

9.2.1.2 嵌套循环联接 工作原理

从概念上讲,嵌套循环相当于两个嵌套的for 循环。

例如,如果一个查询连接了员工 和部门 ,那么伪代码中的嵌套循环可能是:

内部循环对外部循环的每一行执行。employees 表是 外部 数据集,因为它位于外部 for 循环中。外部表有时称为驱动表。 departments 表是 内部 数据集,因为它位于内部的 for 循环中。

嵌套循环连接涉及以下基本步骤:

1. 优化器确定驱动行源并将其指定为外部循环。

外层循环产生一组用于驱动连接条件的行。行源可以是使用索引扫描、全表扫描或任何其他生成行的操作访问的表。

内部循环的迭代次数取决于外部循环中检索到的行数。例如,如果从外部表检索10 行,那么数据库必须在内部表中执行 10 次查找。如果从外部表检索到 10,000,000 行,那么数据库必须在内部表中执行 10,000,000 个查找。

2. 优化器将另一个行源指定为内部循环。

外部循环出现在执行计划的内部循环之前,具体如下:

NESTED LOOPS

outer_loop

inner_loop

3. 对于客户端的每次取数请求,基本过程如下:

a 、从外部行源获取行

b 、探测内部行源以查找与谓词条件匹配的行

c 、重复前面的步骤,直到获取请求获得所有行

有时数据库会对rowid 进行排序,以获得更有效的缓冲区访问模式。

9.2.1.3   嵌套 嵌套循环

嵌套循环的外部循环本身可以是由不同的嵌套循环生成的行源。

数据库可以嵌套两个或更多的外部循环,以便根据需要联接尽可能多的表。每个循环都是一个数据访问方法。下面的模板展示了数据库是如何遍历三个嵌套循环的:

数据库对循环的排序如下:

1. 数据库遍历嵌套循环 1:

嵌套循环1 的输出是一个行源。

2. 数据库遍历嵌套循环 2 ,使用嵌套循环 1 生成的行源作为它的外循环 :

嵌套循环2 的输出是另一个行源。

3. 数据库遍历嵌套循环 3 ,使用嵌套循环 2 生成的行源作为它的外循环 :

9-2 嵌套嵌套循环联接

假设您按如下方式连接employees departments 表:

SELECT   /*+ ORDERED USE_NL(d) */

 e.last_name ,  e.first_name ,  d.department_name

   FROM  employees e ,  departments d

  WHERE  e.department_id =  d.department_id

    AND  e.last_name like   'A%' ;

该计划显示,优化器选择了两个嵌套循环( 步骤 1 和步骤 2) 访问数据 :

在本例中,基本过程如下:

1. 数据库开始遍历内部嵌套循环(步骤 2 ),如下所示:

a 、 数据库在 emp_name_ix 中搜索 rowids ,查找以 a 开头的所有姓氏(步骤 4 )。

例如:

Abel,employees_rowid

Ande,employees_rowid

Atkinson,employees_rowid

Austin,employees_rowid

b. 使用前一步中的 rowid ,数据库从 employees 表中检索一批行 ( 步骤 3) ,例如 :

Abel,Ellen,80

Abel,John,50

这些行成为最内层嵌套循环的外部行源。

批处理步骤通常是自适应执行计划的一部分。要确定嵌套循环是否优于 哈希连接 ,优化器需要确定从行源返回的许多行。如果返回了太多行,那么优化器将切换到不同的连接方法。

c. 对于外部行源中的每一行,数据库扫描dept_id_pk 索引以获得匹配部门ID 的部门中的 rowid( 步骤 5) 并将其连接到employees 行。例如:

Abel,Ellen,80,departments_rowid

Ande,Sundar,80,departments_rowid

Atkinson,Mozhe,50,departments_rowid

Austin,David,60,departments_rowid

这些行成为外部嵌套循环的外部行源( 步骤 1)

2. 数据库遍历外部嵌套循环,如下所示:

a 、 数据库读取外部行源中的第一行。例如:

Abel,Ellen,80,departments_rowid

b 、 数据库使用 departments rowid departments 检索相应的行(步骤 6 ),然后连接结果以获取请求的值(步骤 1 )。

例如:

Abel,Ellen,80,Sales

c 、 数据库读取外部行源中的下一行,使用 departments rowid departments 检索相应的行(步骤 6 ),并遍历循环,直到检索到所有行。

结果集的格式如下:

Abel,Ellen,80,Sales

Ande,Sundar,80,Sales

Atkinson,Mozhe,50,Shipping

Austin,David,60,IT

9.2.1.4 嵌套循环联接的当前实现

Oracle Database 11g 引入了一种新的嵌套循环实现,它减少了物理 I/O 的总体延迟。

当索引或表块不在缓冲区缓存中并且需要处理连接时,需要物理I/O 。数据库可以批处理多个物理 I/O 请求,并使用向量 I/O (数组)而不是一次处理一个。数据库向执行读取的操作系统发送一个 rowid 数组。

作为新实现的一部分,两个嵌套循环连接行源可能出现在执行计划中,而在以前的版本中只有一个会出现在执行计划中。在这种情况下,Oracle 数据库会分配一个嵌套循环连接行源,将连接外部表中的值与内部的索引连接起来。第二行源被分配来联接第一个联接的结果,其中包括存储在索引中的 rowid ,表位于联接的内侧。

考虑嵌套循环联接的原始实现 中的查询。在当前实现中,此查询的执行计划可能如下:

在本例中,hr.departments 表中的行构成内部嵌套循环(步骤 2 )的外部行源(步骤 3 )。索引 emp_department_ix 是内部嵌套循环的内部行源(步骤 4 )。内部嵌套循环的结果构成外部嵌套循环(第 1 行)的外部行源(第 2 行)。 employees 表是外部嵌套循环的外部行源(第 5 行)。

对于每个fetch 请求,基本过程如下:

1. 数据库遍历内部嵌套循环(步骤 2 )以获得获取中请求的行:

a 、 数据库读取第一行部门以获取名为 Marketing Sales 的部门的部门 ID (步骤 3 )。例如:

Marketing,20

此行集合是外部循环。数据库将数据缓存在PGA 中。

b 、 数据库扫描 emp_department_ix employees 表上的一个索引),以查找与此 department ID 相对应的 employees ID (步骤 4 ),然后 连接 结果 (步骤2 )。

结果集的格式如下:

Marketing,20,employees_rowid

Marketing,20,employees_rowid

Marketing,20,employees_rowid

c 、 数据库读取下一行部门,扫描 emp_department_ix 以找到与此部门 ID 相对应的员工行 ID ,然后遍历循环,直到满足客户端请求。

在本例中,数据库只在外部循环中迭代两次,因为只有来自部门的两行满足谓词过滤器。

从概念上讲,结果集具有以下形式:

Marketing,20,employees_rowid

Marketing,20,employees_rowid

Marketing,20,employees_rowid

...

Sales,80,employees_rowid

Sales,80,employees_rowid

Sales,80,employees_rowid

...

这些行成为外部嵌套循环的外部行源(步骤1 )。

此行集缓存在PGA 中。

2. 数据库将上一步获得的 rowid 组织起来,以便在缓存中更有效地访问它们。

3. 数据库开始遍历外部嵌套循环,如下所示:

a 、 数据库从上一步获得的行集合中检索第一行,如下例所示:

Marketing,20,employees_rowid

b 、 使用 rowid ,数据库从 employees 检索一行以获取请求的值(步骤 1 ),如下例所示:

Michael,Hartstein,13000,Marketing

c 、 数据库从行集中检索下一行,使用 rowid 探测匹配行的雇员,并在循环中迭代,直到检索到所有行。

结果集的格式如下:

Michael,Hartstein,13000,Marketing

Pat,Fay,6000,Marketing

John,Russell,14000,Sales

Karen,Partners,13500,Sales

Alberto,Errazuriz,12000,Sales

...

在某些情况下,未分配第二个联接行源,执行计划与Oracle Database 11g 之前的执行计划相同。以下列表描述了这些情况:

索引中存在连接内侧所需的所有列,不需要表访问。在这种情况下, Oracle 数据库只分配一个连接行源。

返回的行的顺序可能与 Oracle Database 12c 之前版本中返回的顺序不同。因此,当 Oracle Database 尝试保留行的特定顺序(例如,为了消除按排序的顺序的需要)时, Oracle Database 可能会使用嵌套循环联接的原始实现。

•OPTIMIZER_FEATURES_ENABLE 初始化 参数设置为Oracle Database 11g 之前的版本。在这种情况下, Oracle Database 使用原始实现进行嵌套循环联接。

9.2.1.5 嵌套循环联接的原始实现

在当前版本中,嵌套循环的新实现和原始实现都是可能的。

对于原始实现的示例,请考虑

hr.employees hr.departments 表:

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 ;

Oracle Database 11g 之前的版本中,此查询的执行计划可能如下所示:

对于每个fetch 请求,基本过程如下:

1. 数据库遍历循环以获取请求的行:

a 、 数据库读取第一行部门以获取名为 Marketing Sales 的部门的部门 ID (步骤 3 )。例如:

Marketing,20

此行集合是外部循环。数据库缓存PGA 中的行。

b 、 数据库扫描 emp_department_ix employees.department_id 列上的索引),以查找与此部门 id 相对应的 employees id (步骤 4 ),然后 连接 结果 (步骤2 )。

从概念上讲,结果集具有以下形式:

Marketing,20,employees_rowid

Marketing,20,employees_rowid

Marketing,20,employees_rowid

c 、 数据库读取下一行部门,扫描 emp_department_ix 以找到与此部门 ID 对应的员工行 ID ,并在循环中迭代,直到满足客户端请求。

在本例中,数据库只在外部循环中迭代两次,因为只有来自部门的两行满足谓词过滤器。

从概念上讲,结果集具有以下形式:

Marketing,20,employees_rowid

Marketing,20,employees_rowid

Marketing,20,employees_rowid

...

Sales,80,employees_rowid

Sales,80,employees_rowid

Sales,80,employees_rowid

...

2. 根据具体情况,数据库可以组织上一步中获得的缓存的 row id ,以便更有效地访问它们。

3. 对于嵌套循环生成的结果集中的每个 employees rowid ,数据库从 employees 检索一行以获取请求的值(步骤 1 )。

因此,基本过程是读取一个rowid 并检索匹配的 employees 行,读取下一个 rowid 并检索匹配的 employees 行,等等。从概念上讲,结果集具有以下形式:

Michael,Hartstein,13000,Marketing

Pat,Fay,6000,Marketing

John,Russell,14000,Sales

Karen,Partners,13500,Sales

Alberto,Errazuriz,12000,Sales

...

9.2.1.6 嵌套循环 控制

可以添加USE _

本文章url:https://www.kukuyl.com/new/30000115.html

相关文章

CopyRight 2018 - 2020 http://www.kukuyl.com 酷酷娱乐网 All Rights Reserved .