Oracle表连接优化思路

Oracle表连接类型：

一、嵌套查询
嵌套循环的算法：在嵌套循环连接中，有驱动顺序，驱动表返回多少条记录，被驱动表就访问多少次，嵌套循环连接中无须排序。
嵌套循环可以快速返回两表关联的前几条数据，如果SQL中添加了HINT：FIRST_ROWS，在两表关联的时候，优化器更倾向于嵌套循环。
在嵌套循环连接,要特别注意驱动表的顺序，小的结果集先访问，大的结果集后访问，才能保证被驱动表的访问次数降到最低，从而提升性能。当两表使用外连接进行关联，如果执行计划是走嵌套循环，那么这时无法更改驱动表，驱动表将会被固定在主表。
驱嵌套循环被驱动表必须走索引。如果嵌套循环被驱动表的连接列没有包含在索引中，那么被驱动表只能走全表扫描，而且是反复多次全表扫描。当被驱动表很大时，SQL就执行不出结果。
嵌套循环被驱动表走索引只能走INDEX UNIQUE SCAN和INDEX RANGE SCAN。嵌套循环被驱动表不能走TABLE ACCESS FULL，不能走INDEX FULL SCAN，不能走INDEX SKIP SCAN，也不能走INDEX FAST FULL SCAN。
嵌套循环查询HINT用法：/+leading(t1) use_nl(t2)/，其中t1为驱动表，t2为被驱动表

SELECT /*+leading(t1) use_nl(t2)*/ * FROM T1 INNER JOIN ON T1.ID = T2.ID

嵌套连接没有连接条件限制

1、两表关联走不走NL是看两个表关联之后返回的数据量多少？还是看驱动表返回的数据量多少？
如果两个表是1：N关系，驱动表位1，被驱动表为N并且N很大，这时即使驱动表返回数据量很少，也不能嵌套循环，因为两表关联之后返回的数据量会很多。所以判断两个表关联是否应该走NL应该直接查看两个表关联之后返回的数据量，如果两个表关联之后返回的数据量少（比如少于万行），可以走NL；返回的数据量多（比如大于万行）。应该走HASH连接。

2、大表是否可以当嵌套循环（NL）驱动表？
可以，大表过滤之后返回的数据量很少就可以充当NL驱动表

3、select * from a inner join b on a.id = b.id；如果a有100条数据，b表有100万行数据，a与b是1：N关系，N很低，应该怎么优化SQL？
因为a与b是1：N关系，N很低，可以在b的连接列（ID）上创建索引。让a与b走嵌套循环（a nl b），这样b表会被扫描100次，但是每次扫描走表的时候走的是id列的索引（范围扫描）。如果让a与b进行hash连接，b表会被全表扫描（因为没有过滤条件），需要查询表中的100万数据，而如果让a和b进行嵌套循环，b表只需要查询出表中最多几百行数据（100*N）。一般情况下，一个小表与一个大表关联，可以考虑小表NL大表，大表走连接列索引（如果大表有过滤条件，需要将过滤条件与连接列组合起来创建组合索引），从而避免大表被全表扫描。
当a与b是1：N关系，N非常大（比如几十万），SQL执行不出结果。主要是a与b关联后返回大量数据，因为返回结果集太多，被驱动表走索引，也就是说该SQL可能是被驱动表走索引返回大量数据导致的性能问题。这时就不能走嵌套循环了，只能走HASH连接，于是用HINT：USE_HASH(A,B)。所以一般来说看到SQL中有distinct ,group by ,count,分析函数，一定要走HASH。因为一般有这些语句，它返回的结果集都非常大。

4、DBLINK永远不能作为NL的被驱动表。

二、哈希连接
两表关联返回少量数据应该走嵌套循环，两表关联返回大量数据应该走HASH连接。
HASH连接的算法：两表等值关联，返回大量数据，将较小的表选为驱动表，将驱动表的“SELECT列和JOIN列”读入PGA中的WORK AREA，然后对驱动表的连接列进行hash运算生成hash table，当驱动表的所有数据完全读入PGA中的WORK AREA之后，再读取被驱动表（被驱动表不需要读入PGA中的WORK AREA中），对被驱动表的连接列也进行hash运算，然后到PGA中的WORK AREA去探测hash table，找到数据就关联上，找不到数据就没关联上。
在HASH连接中，有驱动顺序， 驱动表和被驱动表都只会访问0次或者1次
在HASH连接中，无须排序，消耗PGA内存是因为用于建立HASH表，当驱动表太大、PGA不能完全容纳驱动表时，驱动表就会溢出到临时表空间，进而产生磁盘HASH连接，这时候HASH连接性能会严重下降。
嵌套循环每循环一次，会将驱动表连接列传值给被驱动表的连接列，也就是说嵌套循环会进行传值。HASH连接没有传值的过程列在进行HASH连接的时候，被驱动表的连接列会产生HASH值，到PGA中去探测驱动表所生成的hash table。HASH连接的驱动表与被驱动表的连接列都不需要创建索引。
OLAP环境多数SQL都是大规模的ETL，此类SQL返回的结果集很多，SQL执行计划通常以HASH为主，往往要大量消耗PGA，所以OLAP系统PGA设置较大。
在HASH连接 连接要特别注意驱动表的顺序，小的结果集先访问，大的结果集后访问，才能保证被驱动表的访问次数降到最低，从而提升性能。
嵌套循环查询HINT用法：/+leading(t1) use_hash(t2)/，其中t1为驱动表，t2为被驱动表

SELECT /*+leading(t1) use_hash(t2)*/ * FROM T1 INNER JOIN ON T1.ID = T2.ID。

HASH连接主要用于处理两表等值关联
不支持HASH连接的连接条件：连接条件是<> > <或者LIKE导致HASH连接无法使用

1、怎么优化HASH连接
（1）因为HASH连接需要将驱动表的select列和join列放入PGA中，所以，应该尽量避免书写select * from …语句，将需要的列放在select list中，这样可以减少驱动表对PGA的占用，避免驱动表被溢出到临时表空间，从而提升性能。如果无法避免驱动表被溢出到临时表空间，可以将临时表空间创建在SSD上面或者RAID 0上，加快临时数据的交换速度。
（2）HASH连接驱动表看什么，看体积还是看行数？HASH看体积，NL看行数。HASH看体积，因为HASH是要全部放内存的。HASH因为单个进程最大2G，所以要看体积。
（3）HASH连接驱动表非常大怎么优化？开并行，并行之后就不是一个进程在HASH。

2、HASH连接需要注意地方
(1)HASH JOIN在OLTP环境一般没有什么优化的地方，在OLAP环境中可以利用并行优化HASH JOIN。
（2利用等待事件监控HASH JOIN的时候，如果发现在做on-disk HASH JOIN(direct path read/write temp)，可以加大PGA，或者手工设置work area分配较大的PGA内存。
（3）在做SQL优化的时候，你要检查HASH JOIN的JOIN列（通过HASH JOIN前面的ID去找ACCESS）选择性很低，那么HASH JOIN可能跑很久，这个时候可以尝试构造伪列进行JOIN，如果无法构造伪列，这个时候看看能否从业务上优化，就不要想着SQL优化了。
（4）HASH JOIN选择小表做驱动表，小表指的不是表的行数，而是指的是行数*列宽度。
（5）HASH JOIN只能用于等值连接。
3、一般看到SQL里面有什么要走hash join ?
答：一般看到group by,sum,avg,max,min,distinct,count 就要走hash join，注意这是一般情况下

三、排序合并连接
排序合并连接算法：两表关联，先对两个表根据连接列进行排序，将较小的表作为驱动表（Oracle官方认为排序合并连接没有驱动表），然后从驱动表中取出连接列的值，到已经排好序的被驱动表中匹配数据，如果匹配上数据，就关联成功。驱动表返回多少行，被驱动表就要被匹配多少次，这个匹配过程类似嵌套循环，但是嵌套循环是从被驱动表的索引中匹配数据，而排序合并连接是在内存中（PGA中的work area）匹配数据。
在排序合并连接中，根本没有驱动和被驱动的概念，而嵌套循环连接和HASH连接要考虑驱动和被驱动的情况，在排序合并连接中，需要排序。
如果两表是等值关联，一般不建议走排序合并连接，因为排序合并连接需要将两个表放入PGA中，而HASH连接只需要将驱动表放入PGA中，排序合并连接与HASH连接相比，需要耗费更多的PGA。即使排序合并连接中有一个表走的是INDEX FULL SCAN，另外一个表也需要放入PGA中，而这个表往往是大表，如果走HASH连接，大表会作为被驱动表，是不会被放入PGA中的。因此，两表等值关联，要么走NL（返回数据量少），要么走HASH（返回数据量多），一般情况下不要走SMJ。
排序合并连接HINT用法：/+ordered use_merge(t2)/

SELECT /*+ordered use_merge(t2)*/ * FROM T1 INNER JOIN ON T1.ID = T2.ID

表T1和被表T2都只会访问0次或者1次
不支持排序合并连接的连接条件：连接条件是instr、LIKE、substr、regexp_like排序合并连接无法使用，连接条件是instr、LIKE、substr、regexp_like只能走嵌套循环
支持排序合并连接的连接条件：支持>、>=和<、<=、<>之类的连接条件。
哈希连接和排序合并连接简单的优化思路是：不要取多余的字段参与排序，也就是说不要select查询多余的字段
1、怎么优化排序合并连接？
如果两表关联是等值关联，走的是排序合并连接，可以将表连接方式改为HASH连接。如果两表关联是非等值关联，比如>，>=，<，<=，<>，这时应该从业务下手，尝试将非等值关联改写成等值关联，因为非等值关联返回的结果集“类似”于笛卡尔积，当两个表都比较大的时候，非等值关联返回的数据量相当“恐怖”。如果没有办法将非等值关联改写为等值关联，可以考虑增加两表的现在条件，将两个表数据量缩小，最后可以考虑开启并行查询加快SQL执行速度。
返回大量数据，最好走hash，但是hash有个缺陷就是只能等值连接。排序合并连接就是为了解决非等值关联，并行返回数据量大的情况。

四、笛卡尔连接（CARTESIAN JOIN）
笛卡尔连接算法：两个表没有连接条件的时候就会产生笛卡尔儿积，这种连接方式就叫笛卡尔连接。
在多表关联的时候，两个表没有直接关联条件，但是优化器错误地把某个表返回的ROWS算为1行（注意必须是1行），这个时候也可能发生笛卡尔连接。
1、当执行计划有笛卡尔连接应该怎么优化？
首先应该检查表是否有关联条件，如果表没有关联条件，那么应该询问开发与业务人员为何表没有关联条件，是否为了满足业务需求而故意不写关联条件。
其次应该检查离笛卡尔连接最近的表是否真的返回1行数据，如果返回行数真的只有1行，那么走笛卡尔连接是没有问题的，如果返回行数超过1行，那就需要检查为什么Rows会估算错误（一般是表统计信息没有收集，导致ROWS估算错误），同时纠正错误的ROWS， 纠正错误的ROWS之后，优化器就不会走笛卡尔连接了。

五、标量子查询（SCALAR SUBQUERY）
标量子查询算法：当一个子查询介于select与from之间，这种子查询叫标量子查询。
标量子查询类似一个天然的嵌套循环，而且驱动表固定为主表。嵌套循环被驱动表的连接列必须包含在索引中。同理，标量子查询中子查询的表连接列也必须包含在索引中。
在工作中，尽量避免使用标量子查询，假如主表返回大量数据，主表的连接列基数很高，那么子查询中的表会被多次扫描，从而严重影响SQL性能。如果主表数据量小或者主表的连接列基数很低，那么这个时候也可以用标量子查询，但是记住要给子查询中的连接列建立索引。
当SQL里面有标量子查询，可以将标量子查询等价改写成外连接，从而可以进行HASH连接。

六、半连接（SEMI JOIN）
半连接算法：两个表关联只返回一个表的数据就叫半连接。半连接一般就是指的IN和EXISTS。半连接优化最为复杂

SELECT * FROM SCOTT.DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM SCOTT.EMP);
SELECT * FROM SCOTT.DEPT S WHERE EXISTS (SELECT 1 FROM SCOTT.EMP T WHERE S.DEPTNO = T.DEPTNO);

IN和EXISTS可以等价改写为内连接。

SELECT S.*
  FROM SCOTT.DEPT S
 INNER JOIN (SELECT DEPTNO FROM SCOTT.EMP T GROUP BY DEPTNO) E
    ON S.DEPTNO = E.DEPTNO

在将半连接改写为内连接的时候，要注意主表与子表（子查询的表）的关系。
（1）如果半连接中主表属于1的关系，子表（子查询的表）属于N的关系，在改写为内连接的时候，子表需要加上group by 去重。注意：这个时候半连接性能高于内连接。
（2）如果半连接中主表属于N的关系，子表（子查询的表）属于1的关系，在改写为内连接的时候，子表就不需要去重了。注意：这个时候半连接与内连接性能一样。
（3）如果半连接中主表属于N的关系，子表（子查询的表）属于N的关系，这个时候可以先对子表去重，将子表转化为1的关系，然后再关联，千万不能先关联再去重。

SELECT /*+LEADING(S@A) USE_NL(S@A,T)*/
 GCODE, NAME, IDCODE
  FROM ZHXX_LGY.LY_T_CHREC T
 WHERE GCODE IN (SELECT /*+QB_NAME(A)*/
                  GCODE
                   FROM ZHXX_LGY.LY_T_CHREC S
                  WHERE NAME = '张三'
                    AND BDATE = '19941109')

为何不写HINT/+use_nl(s,t) leading(s)/，因为Oracle数据库中，每个子查询都会自动生成一个查询块（query block），子查询里面的表会自动地被优化器取别名，在子查询中，HINT写成use_nl(s,t)会导致CBO无法识别LY_T_CHREC表，为了让CBO识别到LY_T_CHREC表，在子查询中添加了qb_name这个HINT，给子查询取别名A。

七、反半连接（ANTI JOIN）
反半连接算法：两表关联只返回主表的数据，而且只返回主表与子表没有关联上的数据，这种连接就叫反连接。反连接一般就是指NOT IN 和 NOT EXISTS。

SELECT * FROM SCOTT.DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM SCOTT.EMP);
SELECT * FROM SCOTT.DEPT S WHERE NOT EXISTS (SELECT 1 FROM SCOTT.EMP T WHERE S.DEPTNO = T.DEPTNO);

需要注意的是，NOT IN里面如果有NULL，整个查询会返回空，而IN里面有NULL，查询不受NULL影响。
所以在将NOT EXISTS等价改写为NOT IN的时候，要注意NULL。一般情况下，如果反连接采用NOT IN写法，需要在where条件中剔除NULL。

SELECT * FROM SCOTT.DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM SCOTT.EMP WHERE DEPTNO IS NOT NULL)

NOT IN 和 NOT EXISTS除了可以相互等价改写以外，还可以等价改写为外连接+子表连接条件IS NULL。

SELECT S.*
  FROM SCOTT.DEPT S LEFT JOIN SCOTT.EMP E ON S.DEPTNO = E.DEPTNO
 WHERE E.DEPTNO IS NULL

1、为什么反连接可以改写为“外连接+子表连接条件IS NULL”？
反半连接算法：两表关联只返回主表的数据，而且只返回主表与子表没有关联上的数据。半连接改写为内连接不同的是，反连接改写为外连接不需要考虑两表之间的关系。

八、FILTER
FILTER算法：如果子查询（in/exists/not in/not exists）没能展开（unnest），在执行计划中就会产生FILTER，FILTER类似嵌套循环，FILTER算法与标量子查询一模一样。假如主表返回大量数据，主表的连接列基数很高，那么子查询中的表会被多次扫描。

九、IN与EXISTS谁快谁慢
很多人都受过in与exists谁快谁慢的问题的困扰。如果执行计划中没有产生FILTER，那么可以参考以下思路：in与exists是半连接，半连接也是属于表连接，那么既然是表连接，需要关心的是两表的大小以及两表之间究竟走什么连接方式，还要控制两表的连接方式，才能随心所欲优化SQL。