Oracle Table连接方式分析

可能出现哪些Oracle连接？记住：在Oracle中，连接顺序、可选的索引、用于排序和建立散列表的可用内存的不同都会导致不同的结果。

表连接基本知识：

1、哪张表将驱动查询（即访问的第一张表）？按照指定的路径查询，何时将访问到没一张表？可选的驱动路径有哪些？

2、可能出现哪些Oracle连接？记住：在Oracle中，连接顺序、可选的索引、用于排序和建立散列表的可用内存的不同都会导致不同的结果。

3、哪些索引是可用的?哪些索引是可选的？索引的选择不仅仅将导致优化器使用或者限制一个索引，还将改变驱动查询的方式，并可能决定使用或者限制查询中其他的索引。

4、哪些提示提供了可选的路径？哪些提示限制或强制使用索引？这些提示不仅仅改变了表的驱动顺序，还改变了Oracle中连接的性能，并可决定限制或强制使用哪些索引。

5、您在使用哪一个版本的Oracle？你的选择取决于你使用的Oracle的版本。不同的版本优化器的工作方式也不一样。

--------------------------------------------------------------------------------

Linux-6-64下安装Oracle 12C笔记

在CentOS 6.4下安装Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虚拟机中安装步骤

Debian 下 安装 Oracle 11g XE R2

--------------------------------------------------------------------------------

表连接方式：

在查看sql执行计划时，我们会发现表的连接方式有多种，本文对表的连接方式进行介绍以便更好看懂执行计划和理解sql执行原理。

一、连接方式：

      嵌套循环（Nested  Loops （NL））

      （散列）哈希连接（Hash Join （HJ））

      排序合并连接(Sort Merge Join (SMJ) )

二、连接说明：

    1.Oracle一次只能连接两个表。不管查询中有多少个表，Oracle 在连接中一次仅能操作两张表。

    2.当执行多个表的连接时，优化器从一个表开始，将它与另一个表连接；然后将中间结果与下一个表连接，以此类推，直到处理完所有表为止。

ORACLE从6的版本开始，优化器使用4种不同的表的连接方式：

嵌套循环连接（NESTEDLOOPJOIN）

群集连接（CLUSTERJOIN）

排序合并连接（SORTMERGEJOIN）

笛卡尔连接（CARTESIANJOIN）

哈希连接（HASHJOIN）

索引连接（INDEXJOIN）

　　这六种连接方式都有其独特的技术特点，在一定的条件下，，可以充分发挥高效的性能。

　　但是也都有其局限性，如果使用不当，不仅不能提高效率，反而会严重影响系统的性能。因此，深入地探讨连接方式的内部运行机制对于性能优化是必要的。

　　1、嵌套循环连接

　　嵌套循环连接的内部处理的流程：

　　1）Oracle优化器根据基于规则RBO（rulebasedoptimizer）或基于成本CBO（costbasedoptimizer）的原则，选择两个表中的一个作为驱动表，并指定其为外部表。

　　2）Oracle优化器再将另外一个表指定为内部表。

　　3）Oracle从外部表中读取第一行,然后和内部表中的数据逐一进行对比，所有匹配的记录放在结果集中。

　　4）Oracle读取外部表中的第二行，再和内部表中的数据逐一进行对比，所有匹配的记录添加到结果集中。

　　5）重复上述步骤，直到外部表中的所有纪录全部处理完。

　　6）最后产生满足要求的结果集。

    使用嵌套循环连接是一种从结果集中提取第一批记录最快速的方法。在驱动行源表（就是正在查找的记录）较小、或者内部行源表已连接的列有惟一的索引或高度可选的非惟一索引时，嵌套循环连接效果是比较理想的。嵌套循环连接比其他连接方法有优势，它可以快速地从结果集中提取第一批记录，而不用等待整个结果集完全确定下来。这样，在理想情况下，终端用户就可以通过查询屏幕查看第一批记录，而在同时读取其他记录。不管如何定义连接的条件或者模式，任何两行记录源可以使用嵌套循环连接，所以嵌套循环连接是非常灵活的。

　　然而，如果内部行源表（读取的第二张表）已连接的列上不包含索引，或者索引不是高度可选时，嵌套循环连接效率是很低的。如果驱动表的记录非常庞大时，其他的连接方法可能更加有效。

　　可以通过在SQL语句中添加HINTS,强制ORACLE优化器产生嵌套循环连接的执行计划。

　　select/*+use_nl(ab)*/a.user_name,b.dev_nofromuser_infoa,dev_infobwherea.user_id=b.user_id;

案例：

12:00:44 SCOTT@ prod> select  e.ename,e.sal,d.deptno,d.dname

12:01:50  2    from emp e,dept d

12:01:50  3    where d.deptno=e.deptno and d.deptno=10 ;

ENAME            SAL    DEPTNO DNAME

---------- ---------- ---------- --------------

CLARK            2450        10 ACCOUNTING

KING            5000        10 ACCOUNTING

MILLER          1300        10 ACCOUNTING

Elapsed: 00:00:00.03

Execution Plan

----------------------------------------------------------

Plan hash value: 568005898

----------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time    |

----------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT            |        |    5 |  105 |    4  (0)| 00:00:01 |

|  1 |  NESTED LOOPS                |        |    5 |  105 |    4  (0)| 00:00:01 |

|  2 |  TABLE ACCESS BY INDEX ROWID| DEPT    |    1 |    11 |    1  (0)| 00:00:01 |

|*  3 |    INDEX UNIQUE SCAN        | PK_DEPT |    1 |      |    0  (0)| 00:00:01 |

|*  4 |  TABLE ACCESS FULL          | EMP    |    5 |    50 |    3  (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

  3 - access("D"."DEPTNO"=10)

  4 - filter("E"."DEPTNO"=10)

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

        10  consistent gets

          0  physical reads

          0  redo size

        835  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          3  rows processed

12:01:51 SCOTT@ prod>

11:59:48 SCOTT@ prod>select /*+ use_nl(d e)*/ e.ename,e.sal,d.deptno,d.dname

11:59:49  2    from dept d,emp e

11:59:49  3    where d.deptno=e.deptno ;

14 rows selected.

Elapsed: 00:00:00.03

Execution Plan

----------------------------------------------------------

Plan hash value: 4192419542

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time    |

---------------------------------------------------------------------------

|  0 | SELECT STATEMENT  |      |    14 |  294 |    10  (0)| 00:00:01 |

|  1 |  NESTED LOOPS      |      |    14 |  294 |    10  (0)| 00:00:01 |

|  2 |  TABLE ACCESS FULL| DEPT |    4 |    44 |    3  (0)| 00:00:01 |

|*  3 |  TABLE ACCESS FULL| EMP  |    4 |    40 |    2  (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

  3 - filter("D"."DEPTNO"="E"."DEPTNO")

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

        37  consistent gets

          0  physical reads

          0  redo size

      1038  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        14  rows processed

更多详情见请继续阅读下一页的精彩内容：