Oracle的表连接及优化

表关联中的几种连接方式及对应的优化方法

1、嵌套循环（Nested Loops Join）

2、哈希连接（Hash Join）

3、排序合并（Merge Sort Join）

嵌套循环：驱动表返回几行，被驱动表就访问多少次哈希连接：驱动表和被驱动表都是最多访问1次排序合并：驱动表和被驱动表都是最多访问1次

嵌套循环：有驱动顺序哈希连接：有驱动顺序排序合并：无驱动顺序

嵌套循环：无需排序哈希连接：无需排序（HASH_AREA_SIZE）——会消耗内存用于建HASH表排序合并：无需排序（SORT_AREA_SIZE）

嵌套循环：无任何限制哈希连接：连接条件是<> > < 或者like导致无法使用排序合并：连接条件是<>或者like导致无法使用

嵌套循环：驱动表限制条件有索引，被驱动表连接条件有索引

所谓驱动表即在执行计划的NESTED LOOPS范围内，在前面的表；驱动表的限制条件建索引是为了缩小扫描驱动表的时间，快速返回限制条件的少许几条记录；然后再传递给被驱动表关联，所以在被驱动表的关联列上建索引

哈希连接：索引列在表连接中无特殊要求，与单表情况无异排序合并：索引消除排序

select /*+ leading(t1) use_nl(t2)*/ * from t1,t2 where t1.id = t2.id and t.id = ..

leading(t1)表示强制先访问t1表，即t1表作为驱动表嵌套循环中，驱动表返回多少条记录，被驱动表就访问多少次。所以要特别注意驱动表的顺序（不同的顺序，buffers不同，starts也不同），小的结果集先访问，大的结果集后访问，才能保证被驱动表的访问次数降到最低，提升性能

select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id = t2.id and t.id = ..

在hash连接中，驱动表和被驱动表都只会访问0次或者1次 t2访问0次：数据存在t1表但不存在t2表 t1访问0次：条件1=2 驱动表顺序不同，buffers相同，但Used-Mem有差异，小表做驱动表时，该值小

select /*+ ordered use_merge(t2)*/ * from t1,t2 where t1.id = t2.id and t.id = ..

排序合并连接和hash连接一样，t1表和t2表都只会访问0次或者1次排序合并连接没有驱动表和被驱动表的概念

所以嵌套循环连接和哈希连接有驱动顺序，驱动表的顺序不同将影响表连接的性能，而排序合并连接没有驱动概念，无论哪张表在前都无妨。嵌套循环里没有用到Used-Mem，而哈希和排序合并连接的执行计划里有此关键词，用于哈希连接的建立hash表和排序合并连接的排序。