ORACLE多表连接详解

windjack

ORACLE多表连接详解

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

在多表联合查询的时候，如果我们查看它的执行计划，就会发现里面有多表之间的连接方式。

多表之间的连接有三种方式：Nested Loops，Hash Join 和 Sort Merge Join.

具体适用哪种类型的连接取决于

•     当前的优化器模式 （ALL_ROWS 和 RULE）
•     取决于表大小
•     取决于连接列是否有索引
•     取决于连接列是否排序
  

下面来介绍三种不同连接工作方式的不同：

假如有10000个城市，对应于10个国家（此例子仅解释join工作的过程）

更换优化器，添加索引，会影响下面的执行计划.

drop table country;

CREATE TABLE country (

country_id SMALLINT NOT NULL,

country_name VARCHAR(50) NOT NULL

);

drop table city;

CREATE TABLE city (

city_id VARCHAR(50) NOT NULL,

city_name VARCHAR(50) NOT NULL,

country_id SMALLINT NOT NULL

);

begin

for i in 1 .. 10 loop

insert into country values(i,'country'||i);

end loop;

commit;

end;

/

begin

for i in 1 .. 10000 loop

insert into city values(i,'city'||i,ceil(i/1000));

end loop;

commit;

end;

/

SQL> select count(*) from city;

  COUNT(*)

----------

     10000

SQL> select count(*) from country;

  COUNT(*)

----------

        10

SQL> show parameter optimizer_mode

NAME                                 TYPE                   VALUE

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

optimizer_mode                       string                 ALL_ROWS

一、HASH JOIN:散列连接

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

Hash join散列连接是CBO 做大数据集连接时的常用方式，优化器使用两个表中较小的表（通常是小一点的那个表或数据源）

利用连接键（JOIN KEY）在内存中建立散列表，将列数据存储到hash列表中，然后扫描较大的表，同样对JOINKEY进行HASH后

探测散列表，找出与散列表匹配的行。需要注意的是：如果HASH表太大，无法一次构造在内存中，则分成若干个partition，

写入磁盘的temporary segment，则会多一个写的代价，会降低效率。

这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不

能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有

较大的临时段从而尽量提高I/O 的性能。

可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接。

使用情况：

Hash join在两个表的数据量差别很大的时候.

SQL> select city_name,country_name

  2  from city,country

  3  where city.country_id=country.country_id;

已选择 10000 行。

执行计划

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

Plan hash value: 114462077

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

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

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

|   0 | SELECT STATEMENT   |         | 10000 |   781K|    14   (0)| 00:00:01 |

|*  1 |  HASH JOIN         |         | 10000 |   781K|    14   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| COUNTRY |    10 |   400 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| CITY    | 10000 |   390K|    11   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - access("CITY"."COUNTRY_ID"="COUNTRY"."COUNTRY_ID")

二．SORT MERGE JOIN:排序合并连接

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

Merge Join 是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配。

因为merge join需要做更多的排序，所以消耗的资源更多。 通常来讲，能够使用merge join的地方，hash join都可以发挥更好的性能,即散列连接的效果都比排序合并连接要好。然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。

可以使用USE_MERGE(table_name1 table_name2)来强制使用排序合并连接.

适用情况：

1.RBO模式

2.不等价关联(>,<,>=,<=,<>)

3.HASH_JOIN_ENABLED=false

4. 用在没有索引，并且数据已经排序的情况.

使用情况：

Hash join在两个表的数据量差别很大的时候.

SQL> select city_name,country_name

  2  from country,city

  3  where city.country_id=country.country_id;

已选择 10000 行。

执行计划

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

Plan hash value: 662350140

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

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

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

|   0 | SELECT STATEMENT    |         | 45000 |  1054K|    16  (13)| 00:00:01 |

|   1 |  MERGE JOIN         |         | 45000 |  1054K|    16  (13)| 00:00:01 |

|   2 |   SORT JOIN         |         |    10 |   120 |     4  (25)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| COUNTRY |    10 |   120 |     3   (0)| 00:00:01 |

|*  4 |   SORT JOIN         |         | 10000 |   117K|    12   (9)| 00:00:01 |

|   5 |    TABLE ACCESS FULL| CITY    | 10000 |   117K|    11   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   4 - access(INTERNAL_FUNCTION("CITY"."COUNTRY_ID")<INTERNAL_FUNCTION("

              COUNTRY"."COUNTRY_ID"))

       filter(INTERNAL_FUNCTION("CITY"."COUNTRY_ID")<INTERNAL_FUNCTION("

              COUNTRY"."COUNTRY_ID"))

三．NESTED LOOP:嵌套循环连接

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

Nested loops 工作方式是循环从一张表中读取数据(驱动表outer table)，然后访问另一张表（被查找表 inner table,通常有索引）。

驱动表中的每一行与inner表中的相应记录JOIN。类似一个嵌套的循环。

对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表

中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1 万不适合），要把返回子集较小表的作为外表（CBO 默认外表是驱动表），而且在内表的连接字段上一定要有索引。当然也可以用ORDERED 提示来改变CBO默认的驱动表。

使用USE_NL(table_name1 table_name2)可是强制CBO 执行嵌套循环连接。

适用情况：

适用于驱动表的记录集比较小（<10000）而且inner表需要有有效的访问方法（Index），并且索引选择性较好的时候.

JOIN的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。         

SQL> select city_name,country_name

  2  from country,city

  3  where city.country_id<>country.country_id;

已选择 90000 行。

执行计划

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

Plan hash value: 3145739091

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

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

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

|   0 | SELECT STATEMENT   |         | 90000 |  2109K|   101   (1)| 00:00:01 |

|   1 |  NESTED LOOPS      |         | 90000 |  2109K|   101   (1)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| COUNTRY |    10 |   120 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| CITY    |  9000 |   105K|    10   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   3 - filter("CITY"."COUNTRY_ID"<>"COUNTRY"."COUNTRY_ID")

四、通过HINT来测试三种连接的效率

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

--+leading(table_name)表示指定驱动表

--use_hash/use_nl/use_merge分别表示让ORACLE走hash连接、嵌套循环、排序合并

select /* +leading(city) use_hash(city,country) */ city_name,country_name

from country,city

where city.country_id=country.country_id;

Plan Hash Value  : 114462077

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

| Id  | Operation            | Name    | Rows  | Bytes  | Cost | Time     |

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

|   0 | SELECT STATEMENT     |         | 10000 | 240000 |   14 | 00:00:01 |

| * 1 |   HASH JOIN          |         | 10000 | 240000 |   14 | 00:00:01 |

|   2 |    TABLE ACCESS FULL | COUNTRY |    10 |    120 |    3 | 00:00:01 |

|   3 |    TABLE ACCESS FULL | CITY    | 10000 | 120000 |   11 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 1 - access("CITY"."COUNTRY_ID"="COUNTRY"."COUNTRY_ID")

select /* +leading(country) use_hash(city,country) */city_name,country_name

from country,city

where city.country_id=country.country_id

Plan Hash Value  : 114462077

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

| Id  | Operation            | Name    | Rows  | Bytes  | Cost | Time     |

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

|   0 | SELECT STATEMENT     |         | 10000 | 240000 |   14 | 00:00:01 |

| * 1 |   HASH JOIN          |         | 10000 | 240000 |   14 | 00:00:01 |

|   2 |    TABLE ACCESS FULL | COUNTRY |    10 |    120 |    3 | 00:00:01 |

|   3 |    TABLE ACCESS FULL | CITY    | 10000 | 120000 |   11 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 1 - access("CITY"."COUNTRY_ID"="COUNTRY"."COUNTRY_ID")

两张表走哈希连接时，不管驱动表是谁，对执行结果影响不大。

select /*+leading(city) use_nl(city,country) */city_name,country_name

from country,city

where city.country_id=country.country_id;

Plan Hash Value  : 583620635

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

| Id  | Operation            | Name    | Rows  | Bytes  | Cost  | Time     |

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

|   0 | SELECT STATEMENT     |         | 10000 | 240000 | 13564 | 00:00:01 |

|   1 |   NESTED LOOPS       |         | 10000 | 240000 | 13564 | 00:00:01 |

|   2 |    TABLE ACCESS FULL | CITY    | 10000 | 120000 |    11 | 00:00:01 |

| * 3 |    TABLE ACCESS FULL | COUNTRY |     1 |     12 |     1 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 3 - filter("CITY"."COUNTRY_ID"="COUNTRY"."COUNTRY_ID")

select /*+leading(country) use_nl(city,country) */city_name,country_name

from country,city

where city.country_id=country.country_id

Plan Hash Value  : 3145739091

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

| Id  | Operation            | Name    | Rows  | Bytes  | Cost | Time     |

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

|   0 | SELECT STATEMENT     |         | 10000 | 240000 |  101 | 00:00:01 |

|   1 |   NESTED LOOPS       |         | 10000 | 240000 |  101 | 00:00:01 |

|   2 |    TABLE ACCESS FULL | COUNTRY |    10 |    120 |    3 | 00:00:01 |

| * 3 |    TABLE ACCESS FULL | CITY    |  1000 |  12000 |   10 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 3 - filter("CITY"."COUNTRY_ID"="COUNTRY"."COUNTRY_ID")

两张表走嵌套循环连接时，驱动表（外表）最好使用大表时执行效率更高。

select /*+leading(country) use_merge(city,country) */city_name,country_name

from country,city

where city.country_id=country.country_id;

Plan Hash Value  : 662350140

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

| Id  | Operation             | Name    | Rows  | Bytes  | Cost | Time     |

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

|   0 | SELECT STATEMENT      |         | 10000 | 240000 |   16 | 00:00:01 |

|   1 |   MERGE JOIN          |         | 10000 | 240000 |   16 | 00:00:01 |

|   2 |    SORT JOIN          |         |    10 |    120 |    4 | 00:00:01 |

|   3 |     TABLE ACCESS FULL | COUNTRY |    10 |    120 |    3 | 00:00:01 |

| * 4 |    SORT JOIN          |         | 10000 | 120000 |   12 | 00:00:01 |

|   5 |     TABLE ACCESS FULL | CITY    | 10000 | 120000 |   11 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 4 - access("CITY"."COUNTRY_ID"="COUNTRY"."COUNTRY_ID")

* 4 - filter("CITY"."COUNTRY_ID"="COUNTRY"."COUNTRY_ID")

select /*+leading(city) use_merge(city,country) */city_name,country_name

from country,city

where city.country_id=country.country_id;

Plan Hash Value  : 387422054

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

| Id  | Operation             | Name    | Rows  | Bytes  | Cost | Time     |

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

|   0 | SELECT STATEMENT      |         | 10000 | 240000 |   16 | 00:00:01 |

|   1 |   MERGE JOIN          |         | 10000 | 240000 |   16 | 00:00:01 |

|   2 |    SORT JOIN          |         | 10000 | 120000 |   12 | 00:00:01 |

|   3 |     TABLE ACCESS FULL | CITY    | 10000 | 120000 |   11 | 00:00:01 |

| * 4 |    SORT JOIN          |         |    10 |    120 |    4 | 00:00:01 |

|   5 |     TABLE ACCESS FULL | COUNTRY |    10 |    120 |    3 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 4 - access("CITY"."COUNTRY_ID"="COUNTRY"."COUNTRY_ID")

* 4 - filter("CITY"."COUNTRY_ID"="COUNTRY"."COUNTRY_ID")

两张表走排序合并时，不管驱动表是谁，对执行结果没有影响。

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

小结：

从以上可看出，NL连接方式对连接表数据量大小比较敏感，建议驱动表为大表。

但是生产中经常出现NL连接时反而效率更低的情况，多数原因与统计信息不准导致ORACLE评估偏差而走了错误的多表关联方式。

另外，大表关联查询最好为条件列添加有效的索引也可以大大提升查询效率。

下图展示一个异常场景：

登录/注册后可看大图