前言：
驱动表或者查询中的第一张访问的表是获得更佳性能的关键。如果优化器指定了错误的表作为查询中的驱动表，那么造成的差异会有天壤之别。
通常，基于成本的优化器会选择正确的表，但在表中建立的的索引将影响它的正常工作。如果需要使用提示改变驱动表，这个现象说明需要改进索引计划。
在一个有10张表的连接表中，将可能有3628800种组合，尝试所有的组合是非常耗时的举措。虽然可以使用LEADING提示来处理这个烦人的事，但与一开始就建立正确的索引
模式相比，其代价仍然非常巨大。
1.连接方法
【必须了解的知识】
1）哪张表将驱动查询（即访问的第一个表）？按照指定的路径查询，何时将访问到每一张表？可选的驱动路径有哪些？
2）可能出现哪些Oracle连接？记住：在Oracle中，连接顺序、可选的索引、用于排序和建立散列表的可用内存的不同都会导致不同的结果。
3）哪些索引是可用的？哪些索引是可选的？索引的选择不仅仅将导致优化器使用或都限制一个索引，还将改变驱动查询的方式，并可能确定决定使用或者
限制查询中其他的索引。
4）哪些提示提供了可选的路径？哪些提示限制或者强制使用索引？这些提示不仅改变了表的驱动顺序，还改变了Oracle中连接的性能，并可决定限制或
强制使用哪些索引。
5）您在使用哪一个版本的Oracle？您的选择取决于您使用的Oracle版本。不同的版本优化器的工作也不一样。
1.1嵌套循环连接
在嵌套循环连接中，Oracle从第一个行源中读取第一行，然后和第二个行源中的数据进行对比。所有匹配的记录放在结果集中，然后Oracle将读取第一个行源中的下一行。
按这种方式直至第一个数据源中的所有行都经过处理。第一个行源通常称为外部表，或者驱动表，相应的第二个行源称为内部表。
在驱动行源表较小、或者内部行源表已连接的列有唯一的索引或高度可选的非唯一索引时，嵌套循环连接效果是比较理想的。嵌套循环连接相对于其他连接方法的优势是，
它可以快速地从结果集中提取第一批记录，而不用等待整个结果集完全确定下来。
1.2排序合并连接
在排序合并连接中，Oracle分别将第一个源表、第二个源表按它们各自要连接的列排序，然后将两个已经排序的源表合并。如果找到匹配的数据，就放到结果集中。
在缺乏数据的选择性或者可用的索引时，或者两个源表都过于庞大（超过记录数的5%）时，排序合并连接将比嵌套循环连接更加高效。但是，排序合并连接只能用于
等价连接（WHERE D.deptno=e.deptno，而不是WHERE D.deptno>=e.deptno）。排序合并连接需要临时的内存块，以用于排序。
【实验：排序合并连接与嵌套循环连接比较】
同尺寸表的比较：
1）
create table dept_sort
as
select * from dept;
2）使用排序合并
SQL> select /*+ordered use_merge(t,e) no_index(t)*/ count(*)
2 from scott.dept t
3 inner join scott.dept_sort e on(t.deptno=e.deptno);
COUNT(*)
----------
43105
Execution Plan
----------------------------------------------------------
Plan hash value: 3225577987
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 10 | | 418 (4)|
00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|
| 2 | MERGE JOIN | | 43105 | 420K| | 418 (4)|
00:00:06 |
| 3 | SORT JOIN | | 43105 | 210K| 1032K| 225 (4)|
00:00:03 |
| 4 | TABLE ACCESS FULL| DEPT | 43105 | 210K| | 86 (2)|
00:00:02 |
|* 5 | SORT JOIN | | 43105 | 210K| 1032K| 193 (4)|
00:00:03 |
| 6 | TABLE ACCESS FULL| DEPT_SORT | 43105 | 210K| | 54 (2)|
00:00:01 |
--------------------------------------------------------------------------------
----------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T"."DEPTNO"="E"."DEPTNO")
filter("T"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
664 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
3）嵌套循环连接
SQL> select /*+ordered use_nl(t,e) no_index(t)*/ count(*)
2 from scott.dept t
3 inner join scott.dept_sort e on(t.deptno=e.deptno);
Execution Plan
----------------------------------------------------------
Plan hash value: 1656245853
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 10 | 2277K (3)| 07:35:31
|
| 1 | SORT AGGREGATE | | 1 | 10 | |
|
| 2 | NESTED LOOPS | | 43105 | 420K| 2277K (3)| 07:35:31
|
| 3 | TABLE ACCESS FULL| DEPT | 43105 | 210K| 86 (2)| 00:00:02
|
|* 4 | TABLE ACCESS FULL| DEPT_SORT | 1 | 5 | 53 (2)| 00:00:01
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T"."DEPTNO"="E"."DEPTNO")
4）HASH连接
SQL> select /*+ordered use_hash(t,e) no_index(t)*/ count(*)
2 from scott.dept t
3 inner join scott.dept_sort e on(t.deptno=e.deptno);
Execution Plan
----------------------------------------------------------
Plan hash value: 1900602432
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 10 | 143 (4)| 00:00:02
|
| 1 | SORT AGGREGATE | | 1 | 10 | |
|
|* 2 | HASH JOIN | | 43105 | 420K| 143 (4)| 00:00:02
|
| 3 | TABLE ACCESS FULL| DEPT | 43105 | 210K| 86 (2)| 00:00:02
|
| 4 | TABLE ACCESS FULL| DEPT_SORT | 43105 | 210K| 54 (2)| 00:00:01
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
664 consistent gets
35 physical reads
0 redo size
413 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
【实验小结】
1.同尺寸表，无索引情况下。USE_MERGE的COST为418，consistent gets为664。USE_NL的代价为2277K。COST相差5400多倍。
2.第2步中，用到了TempSpc1032k.
3.从USER_EXTENT可知，DEPT与DEPT_SORT的实际空间各300多个块，排序组合连接的consistent gets与两表的大小之合十分接近。
4.HASH连接的COST最小。
1.3集群连接
【实验：连接顺序】
SQL> select
2 t2.sno,t2.ltext
3 from scott.staff t1
4 left join scott.address_lines t2 on(t1.sno=t2.sno)
5 where t1.sno=2000;
SNO
----------
LTEXT
--------------------------------------------------------------------------------
2000
address1
2000
address2
Execution Plan
----------------------------------------------------------
Plan hash value: 3180723558
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 2 | 34 | 2 (0)| 00:
00:01 |
| 1 | NESTED LOOPS OUTER | | 2 | 34 | 2 (0)| 00:
00:01 |
|* 2 | INDEX UNIQUE SCAN | STAFF_PK | 1 | 4 | 1 (0)| 00:
00:01 |
|* 3 | TABLE ACCESS CLUSTER| ADDRESS_LINES | 2 | 26 | 1 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."SNO"=2000)
3 - filter("T2"."SNO"(+)=2000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
【实验小结】
1）索引集群使用的是嵌套循环连接
2）首先访问驱动表的第一条记录，再到第二个表中查找与之相关联的记录。查找第二张表的数据时，不需要执行新的I/O，因为两张表的关联数据存储在相同的数据块中。
3）从实验结果来看，在访问索引集群的时候并没有访问集群上的索引。或许索引是作用是构建集群。
1.4 HASH连接
在散列连接中，Oracle访问一张表（通常是连接结果中较小的表），并在内存中建立一张基于连接键的散列表。然后它扫描连接中其他的表（通常是较大的表），
并根据散列表检测是否有匹配的记录。
散列连接与嵌套循环连接有点类似，因为其中发生嵌套循环-----Oracle先建立一张散列表以利于操作进行，然后循环访问散列表。当使用ORDERED提示时，FROM
子句中的第一张表将用于建立散列表。
当缺少有用的索引时，散列连接比嵌套循环连接更加有效。散列连接可能比排序合并连接更快，因为在这种情况下只有一张源表需要排序。散列连接也可能比嵌套
循环连接更快，因为处理内存中的散列表比检索B-树索引更加迅速。和排序合并连接、集群连接一样，散列连接只能用于等价连接。和排序合并连接一样，散列连接
使用内存资源，当用于排序内存不足时，会增加临时表空间的I/O。
【实验1】HASH连接与排序合并比较
见1.2的实验
【实验小结】排序与创建散列表的COST对比：
排序合并连接中，扫描DEPT的COST为86，排序后的COST为225；
散列连接中，扫描DEPT的COST为86，扫描DEPT_SORT的COST为54，两表连接后的COST为143。可以，创建HASH表几乎没有消耗COST。
【实验2】HASH连接与嵌套循环连接的比较：不同尺寸表比较,且驱动表非常小时
1）HASH
SQL> select /*+ use_hash(e,t)*/ e.*,t.deptno
2 from scott.dept e,scott.dept2 t
3 where t.deptno=e.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 1735693636
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 90 (4)| 00:00:02 |
|* 1 | HASH JOIN | | 1 | 41 | 90 (4)| 00:00:02 |
| 2 | TABLE ACCESS FULL| DEPT2 | 3 | 9 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 43105 | 1599K| 87 (3)| 00:00:02 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
410 consistent gets
405 physical reads
0 redo size
866 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
2）NESTED LOOPS
SQL> select /*+ use_NL(e,t)*/ e.*,t.deptno
2 from scott.dept e,scott.dept2 t
3 where t.deptno=e.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 3243467368
--------------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | 1 | 41 | 2 (0)|
00:00:01 |
| 1 | NESTED LOOPS | | 1 | 41 | 2 (0)|
00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT2 | 3 | 9 | 2 (0)|
00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 38 | 0 (0)|
00:00:01 |
|* 4 | INDEX UNIQUE SCAN | IDX_UNIQUE | 1 | | 0 (0)|
00:00:01 |
--------------------------------------------------------------------------------
-----------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12 consistent gets
5 physical reads
0 redo size
863 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
【实验小结】相驱动表很小，内部表存在有效的索引时，嵌套循环效率最高。
【实验3】HASH连接与嵌套循环连接的比较：两张表的尺寸表都比较大
1）NESTED LOOPS
SQL> select /*+ordered use_nl(t,e) no_index(t)*/ count(*)
2 from scott.dept t
3 inner join scott.dept_sort e on(t.deptno=e.deptno);
Execution Plan
----------------------------------------------------------
Plan hash value: 1503069793
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 43241 (1)| 00:08:39 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| 2 | NESTED LOOPS | | 43105 | 420K| 43241 (1)| 00:08:39 |
| 3 | TABLE ACCESS FULL| DEPT | 43105 | 210K| 86 (2)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | IND_DEP | 1 | 5 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
43607 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2）HASH
SQL> select /*+ordered use_hash(t,e) no_index(e) no_index(t)*/ count(*)
2 from scott.dept t
3 inner join scott.dept_sort e on(t.deptno=e.deptno);
Execution Plan
----------------------------------------------------------
Plan hash value: 1900602432
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 10 | 143 (4)| 00:00:02
|
| 1 | SORT AGGREGATE | | 1 | 10 | |
|
|* 2 | HASH JOIN | | 43105 | 420K| 143 (4)| 00:00:02
|
| 3 | TABLE ACCESS FULL| DEPT | 43105 | 210K| 86 (2)| 00:00:02
|
| 4 | TABLE ACCESS FULL| DEPT_SORT | 43105 | 210K| 54 (2)| 00:00:01
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
664 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
【实验3小结】处理内存中的散列表比检索B-树索引更加迅速。
1.5索引连接
从Oracle 8i起，如果一组已存在的索引包含了查询所需要的所有信息，那么优化器将在索引中有选择地生成一组散列表。可通过范围扫描或者快速全局扫描访问到每一
个索引，而选择何种扫描方式取决于WHERE子句中的可用条件。
相对于快速全局扫描，连接索引的优势在于：快速全局扫描只有一个单一索引满足整个查询。索引连接可以有多个索引满足整个查询。尽管快速全局扫描在有时可能更快，
但大多数情况下还是推荐使用索引连接。INDEX_FFS经常会出现问题，因为它会扫描整个索引。
通常可能是B-树索引之间的连接。多个位图索引做的是BITMAP AND。
【实验】：索引连接（B树索引与位图索引可不可以连接）
SQL> select /*+index_join(t idx_unique BIT_IDX_CITY )*/ t.deptno,city
2 from scott.dept t
3 where t.deptno<1000 and t.city='6号城市';
96 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2872868837
--------------------------------------------------------------------------------
------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
------------------
| 0 | SELECT STATEMENT | | 98 | 1274 | 6
(17)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 98 | 1274 | 6
(17)| 00:00:01 |
|* 2 | HASH JOIN | | | |
| |
| 3 | BITMAP CONVERSION TO ROWIDS| | 98 | 1274 | 2
(0)| 00:00:01 |
|* 4 | BITMAP INDEX SINGLE VALUE | BIT_IDX_CITY | | |
| |
|* 5 | INDEX RANGE SCAN | IDX_UNIQUE | 98 | 1274 | 4
(25)| 00:00:01 |
--------------------------------------------------------------------------------
------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."DEPTNO"<1000 AND "T"."CITY"='6号城市')
2 - access(ROWID=ROWID)
4 - access("T"."CITY"='6号城市')
5 - access("T"."DEPTNO"<1000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
2114 bytes sent via SQL*Net to client
451 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96 rows processed
--B-树索引转换成位图后，进行BITMAP AND
SQL> select /*+index(t BIT_IDX_CITY )*/ t.deptno,city
2 from scott.dept t
3 where t.deptno<1000 and t.city='6号城市';
96 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1454350302
--------------------------------------------------------------------------------
-----------------
| Id | Operation | Name | Rows | Bytes | Cost (
%CPU)| Time |
--------------------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT | | 98 | 1274 | 28
(4)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 98 | 1274 | 28
(4)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | |
| |
| 3 | BITMAP AND | | | |
| |
|* 4 | BITMAP INDEX SINGLE VALUE | BIT_IDX_CITY | | |
| |
| 5 | BITMAP CONVERSION FROM ROWIDS| | | |
| |
| 6 | SORT ORDER BY | | | |
| |
|* 7 | INDEX RANGE SCAN | IDX_UNIQUE | | | 3
(0)| 00:00:01 |
--------------------------------------------------------------------------------
-----------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T"."CITY"='6号城市')
7 - access("T"."DEPTNO"<1000)
filter("T"."DEPTNO"<1000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
106 consistent gets
0 physical reads
0 redo size
2114 bytes sent via SQL*Net to client
451 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
96 rows processed
【实验小结】
1.B-树索引与位图索引也可以进行索引连接。
2.作为选择，B-树索引可以转换成位图索引，进行位图计算(BITMAP AND)。但是测试结果表明，这样做的效率不高。
2.表连接的初始化参数
初始化参数DB_FILE_MULTIBLOCK_READ_COUNT定义了当Oracle执行一系列读操作（例如全表扫描）时，每次应当从磁盘读取多少数据块。因为排序合并连接经常
牵涉到全表扫描，设置这个参数后，在扫描大表时将降低开销。
初始化参数PGA_aggregate_TARGET定义了用于排序的内存，这个参数对所有的排序性能有重要影响。
【主要的连接方法】
----------------------------------------------------------------------------------------------------------------------------------------------
类别 | 嵌套循环连接 | 排序合并连接 | 散列连接
优化器提示 | USE_NL | USE_MERGE | USE_HASH
何时使用 | 任何连接 | 任何连接 | 仅用于等价连接
相关资源 | CPU、磁盘I/O | 内存、临时空间 | 内存、临时空间
| | |
特点 | 当有高选择性索引或者进行限制性 | 当缺乏索引或者搜索条件模糊时，该 | 当缺乏索引或者搜索条件模糊时，该类型的连接
| 搜索时效率较高，用于快速返回第 | 类型的连接比嵌套循环连接有效。可以 | 比嵌套循环连接有效。通常比排序合并连接快。
| 一次的搜索结果 | 在有限的内存下工作。 |
| | |
缺点 | 当索引丢失或者查询条件限制不够 | 所有的表都需要排序。它为最优化的吞吐 | 为建立散列表，需要大量内存，第一次的结果返回
| 时，效率很低。 | 量而设计，并且在结果没有全部找到前不 | 较慢。如果在磁盘上操作速度将极慢。
| | 返回数据。 |
----------------------------------------------------------------------------------------------------------------------------------------------
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7901922/viewspace-1060012/ ，如需转载，请注明出处，否则将追究法律责任。
