Sql优化(二) 关联(join)

当sql访问多个表时，关联对sql效率就有很重要的影响。关联要考虑两个因素，join的类型和join的次序。
一、 JOIN的分类
(一) Nested loop join
1. 适用条件
1)关联少量数据(rows)，返回集小
2)关联条件能高效访问第二张表(inner table)。高效访问的关联条件如'='，反之非高效的关联条件如'!=','>'等;inner table（即非驱动表）上要有索引。
因此比较 适合OLTP系统，因为OLTP系统中一般返回数据量小，而且表上面索引较多。[@more@]

2. 实现步骤
1) 优化器选择驱动表(driving table)，指定其为outer table
2) 指定另一张表为inner table(非驱动表)
3) 根据outer table的每行记录的关联字段，来访问inner table。如下所示：
NESTED LOOPS
outer_loop
inner_loop
由于Nested loop从outer table向inner table查询，关联的次序就比较重要了。
3. nested loop join的例子
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;
-------------------------------------------------------------------------------------------------
| 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 |
-------------------------------------------------------------------------------------------------

(二) Hash join
1. 适用条件
1) 仅用于等值关联equijoin（如=）;
2) 满足下列任一条件：
大表关联
或者小表的大部分记录参与关联
2. 实现机制
1) 优化器选择较小的表，基于join key构建hash table。（驱动表）
2) 扫描另外一张较大的表，并在hash table中搜寻关联行
如果内存足够，小表全部在内存中，这种情况是最优的，成本可估算为两张表各一次全表读。
如果内存不够，则小表的一部分可以放在temporary tablespace中（Temp表空间应足够大），以尽可能提高io速度。

3. 例子
SELECT o.customer_id, l.unit_price * l.quantity
FROM orders o ,order_items l
WHERE l.order_id = o.order_id;

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 665 | 13300 | 8 (25)|
|* 1 | HASH JOIN | | 665 | 13300 | 8 (25)|
| 2 | TABLE ACCESS FULL | ORDERS | 105 | 840 | 4 (25)|
| 3 | TABLE ACCESS FULL | ORDER_ITEMS | 665 | 7980 | 4 (25)|
--------------------------------------------------------------------------

(三) Sort merge join
1. 适用情况
通常情况下hash join性能更好，但如果关联的数据已经排序或不需排序，则sort merge join性能会更好
非等值关联（nonequi join,如 ）时很有用，因为sort merge join在返回集很大时比nested loop性能好，而hash join又只能在equijoin中使用。
2. 实现机制
1) Sort操作：关联数据按照关联字段进行排序。如果数据本来就是排序的，就不需此操作
2) Merge操作：经过排序的数据进行merge操作。
需要说明的是,sort merge join没有driving table的概念
(四) 笛卡尔连接
无关联条件，应尽可能避免。
(五) Outer join
是simple join的扩展，
SELECT cust_last_name, sum(nvl2(o.customer_id,0,1)) "Count"
FROM customers c, orders o
WHERE c.credit_limit > 1000
AND c.customer_id = o.customer_id(+)
C表称为preserved table,o表称为optional table
Outer join分为：
Left outer join
Right outer join
Full outer join
和普通join相比，outer join也可以是nested loop、hash join、sort merge等。但有一些不同之处：
1. Nested loop outer join中，以preserved table作为驱动表，而不是像普通join基于cost来选择驱动表
2. Full outer join（equijoin）在11g中，自动使用基于hash join的算法。执行计划中出现HASH JOIN FULL OUTER。
可以用hint:NATIVE_FULL_OUTER_JOIN/NO_NATIVE_FULL_OUTER_JOIN来指定使用或不使用这一算法。如果不使用，则full outer jion的执行计划是left outer join和right outer jion的union。

二、 Join次序

基本原则是：记录少的先关联，这样参与后续关联的记录数就会少。具体来说：
1)选择能排除掉最多记录的表作为driving table
2)剩余的表中，选选择有最好的filter的表（排除最多记录）作为首先参与关联的表
3)以此类推
看这个例子：
SELECT info
FROM taba a, tabb b, tabc c
WHERE a.acol BETWEEN 100 AND 200
AND b.bcol BETWEEN 10000 AND 20000
AND c.ccol BETWEEN 10000 AND 20000
AND a.key1 = b.key1
AND a.key2 = c.key2;假设a表经过filter后记录最少，b次之，c记录最多。那么可以用a作为driving table,先与b关联，最后与c关联

三． 使用hint选择关联方式和次序
(一)使用hint指定关联方式
Oracle优化器自动选择join的方式，但有时不是最优的，开发人员可使用hint来选择join方式，比较执行效率。相关的hint有：
USE_NL,USE_HASH,USE_MERGE
Exists子句中，HASH_SJ,MERGE_SJ,NL_SJ
Not in子句中，HASH_AJ,MERGE_AJ，NL_AJ

(二) 使用hint指定关联次序
如果oracle优化器选择的关联次序不是你所希望的，可以用hint（leading和ordered）来指定。Ordered表示按照sql语句中表出现的先后次序，leading则可任意指定，更为通用。
Leading指定了driving table的选定次序。（在nested loop中,driving table就是outer table，在hash join中，是hash table。）
SELECT /*+ leading (a b c) */info
WHERE a.acol BETWEEN 100 AND 200
AND b.bcol BETWEEN 10000 AND 20000
AND c.ccol BETWEEN 10000 AND 20000
AND a.key1 = b.key1
AND a.key2 = c.key2;

(三) Undocumented hint参数:swap_join_inputs
注意，上面例子中，a作为驱动表和b关联，关联结果作为驱动表，再和c关联。有时需要改变次序,如下面例子
SELECT /*+ leading (a b c)*/ info
WHERE a.key1 = b.key1
AND b.key2 = c.key2;
假如a 1000条，b 10万条，c 1万条。由于a和c表没有关联字段，因此a和b先关联，再和c关联。但a关联b产生2万条记录，和c关联时，希望以c为驱动表，能否实现呢？
在hash_join中可以用oracle的隐含hint参数swap_join_inputs实现：
SELECT /*+ leading (a b c) swap_join_inputs(c) */ info
WHERE a.key1 = b.key1
AND b.key2 = c.key2;
详见metalink：How to switch the driving table in a hash join [ID 171940.1]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18474/viewspace-1060728/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18474/viewspace-1060728/