ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle表连接操作——Merge Sort Join(合并排序连接)

Oracle表连接操作——Merge Sort Join(合并排序连接)

原创 Linux操作系统 作者:realkid4 时间:2011-06-02 22:24:58 0 删除 编辑

 

关系型数据库并不是最早出现的数据库表现形式,之前还存在层次、网状数据库结构。随着关系型数据库的出现,以数据表的方式进行信息留存的方案迅速发展起来。关系型数据库的重要元素包括数据表和表连接,借助各种类型的表连接,可以将平铺直叙的信息加以组装拼接。

 

 

1Merge Sort Join原理机制

 

Nest Loop Join嵌套循环是一种比较古老的连接匹配方式,特点是通过两层的循环结构,将符合条件的数据行整理出来。嵌套循环的最大缺陷之一,就是伴随着驱动表被驱动表之间的选择,以及大量随机读现象。

 

 

Merge Sort Join连接的优势就是可以一定程度上减少随机读的情况。合并排序连接的最大特征是在一次扫描的同时,就判断连接。不会像Nest Loop Join那样频繁的进行数据读取。使用这种方式的前提,就是连接的两个数据集合必须按照连接列的顺序进行排序。具体操作流程如下:

 

ü        Merge Sort Join连接而言,不存在驱动表和被驱动表的问题。两边的数据集合没有顺序区别,都要进行排序操作;

ü        根据Oracle排序规则和方法,按照连接列的顺序对两个数据集合进行排序;

ü        依次对两边的数据集合进行扫描,由于已经是排序过得结果,可以直接确定连接条件是否匹配;

ü        确定进行连接的两端数据行,再依据筛选列的要求获取数据;

 

下面是一个进行Merge Sort Join的执行计划:

 

//使用Merge Sort Join方法

SQL> select /*+use_merge(segs,tabs)*/* from segs, tabs where segs.segment_name=tabs.table_name;

已选择865行。

 

执行计划

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

Plan hash value: 3475644097

 

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

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

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

|   0 | SELECT STATEMENT    |      |   990 |   354K|       |   144   (2)| 00:00:02 |

|   1 |  MERGE JOIN         |      |   990 |   354K|       |   144   (2)| 00:00:02 |

|   2 |   SORT JOIN         |      |   968 |   229K|   712K|    65   (2)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| TABS |   968 |   229K|       |    11   (0)| 00:00:01 |

|*  4 |   SORT JOIN         |      |  2267 |   274K|   824K|    79   (2)| 00:00:01 |

|   5 |    TABLE ACCESS FULL| SEGS |  2267 |   274K|       |    13   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   4 - access("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")

       filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")

统计信息

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

       2010  recursive calls

          0  db block gets

        378  consistent gets

          0  physical reads

          0  redo size

      72346  bytes sent via SQL*Net to client

       1003  bytes received via SQL*Net from client

         59  SQL*Net roundtrips to/from client

         10  sorts (memory)

          0  sorts (disk)

        865  rows processed

 

//使用嵌套循环;

SQL> select /*+use_nl(segs,tabs)*/* from segs, tabs where segs.segment_name=tabs.table_name;

已选择865行。

执行计划

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

Plan hash value: 840690564

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

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

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

|   0 | SELECT STATEMENT   |      |   990 |   354K| 11075   (1)| 00:02:13 |

|   1 |  NESTED LOOPS      |      |   990 |   354K| 11075   (1)| 00:02:13 |

|   2 |   TABLE ACCESS FULL| TABS |   968 |   229K|    11   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| SEGS |     1 |   124 |    11   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   3 - filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")

统计信息

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

       1930  recursive calls

          0  db block gets

      43978  consistent gets

          0  physical reads

          0  redo size

      70556  bytes sent via SQL*Net to client

       1003  bytes received via SQL*Net from client

         59  SQL*Net roundtrips to/from client

          6  sorts (memory)

          0  sorts (disk)

        865  rows processed

 

 

上面代码示例中给出了两个执行计划,给我们如下的信息。

 

首先,我们观察使用use_merge提示的SQL,在Hint的作用下,CBO生成的执行计划中使用Merge Sort Join连接方式。在执行计划中Oracle对两个数据表进行Sort操作,之后对排序过的结果进行Merge连接。其中Oracle对两个数据表进行的都是全表扫描操作。

 

 

另一个执行计划是使用use_nl控制的Nest Loop Join连接方式。中间同样也是没有使用索引等方式。其中,产生了大量逻辑读。见下表对比:

 

对比项目

Merge Sort Join

Nest Loop Join

逻辑读consistent gets

378

43978

排序空间sort

10

6

 

通过数据信息的对比,我们可以明显的看出两个相同结果集合的SQL,由于不同的连接方式而带来的差异。Merge Sort Join可以大大消除由于Nest Loop Join带来的随机读过多的情况。而由于进行的排序操作,Merge Sort Join也要付出相应的排序空间损耗。

 

2Merge Sort Join与排序空间

 

Oracle熟悉的朋友们通常对SortGroup操作是比较敏感的。SortGroup by都是需要单独对数据集进行的操作,要消耗额外的CPU和内存资源。CPU资源主要消耗在算法排序和结果集合整合上。而内存资源的消耗更加需要关注,排序操作要在专门的PGA排序区内完成。如果PGA中特定的排序大小(pga_aggregat_target:sort_area_size)不足以进行排序操作,也就是说需要排序分组的数据集合特别大的时候,Oracle需要调用Temp表空间的容量来进行操作。

 

 

这也就是问题的所在。Temp表空间数据存储位于磁盘中,速度与内存相差很多。所以,当进行排序操作的数据集合很大,会出现性能急剧的下降可能。在实际业务场景中,对海量数据集合的处理、Data Warehouse应用的操作,都可能出现这种情况。

 

回到Merge Sort Join来,就可以理解这种连接方式的缺陷之处了。要进行Merge Sort Join,其中的Sort过程不可避免。而使用Sort操作带来的优势就是不需要进行过多的随机读。在数据集合量很大的时候,Merge Sort Join的效率可能会很差。

 

 

3、对索引路径的借用

 

Nest Loop Join中,对连接列进行索引处理,可以很大程度上提升执行计划效率,减少随机读的数量。道理就是借用了索引排序这个现实。而Merge Sort Join对索引的应用效果远不如Nest Loop Join

 

索引环境构建:

 

//索引构建

SQL> create index idx_tabs_name on tabs(table_name);

Index created

 

SQL> create index idx_segs_name on segs(segment_name);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'SEGS',cascade => true);

PL/SQL procedure successfully completed

 

SQL> exec dbms_stats.gather_table_stats(user,'TABS',cascade => true);

PL/SQL procedure successfully completed

 

 

执行计划如下:

 

 

SQL> explain plan for select /*+use_merge(tabs,segs)*/* from segs,tabs where segs.segment_name=tabs.table_name;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 3475644097

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

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

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

|   0 | SELECT STATEMENT    |      |   990 |   354K|       |   144   (2)| 00:00:

|   1 |  MERGE JOIN         |      |   990 |   354K|       |   144   (2)| 00:00:

|   2 |   SORT JOIN         |      |   968 |   229K|   712K|    65   (2)| 00:00:

|   3 |    TABLE ACCESS FULL| TABS |   968 |   229K|       |    11   (0)| 00:00:

|*  4 |   SORT JOIN         |      |  2267 |   274K|   824K|    79   (2)| 00:00:

|   5 |    TABLE ACCESS FULL| SEGS |  2267 |   274K|       |    13   (0)| 00:00:

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

Predicate Information (identified by operation id):

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

   4 - access("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")

       filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")

18 rows selected

 

 

由于Merge Sort Join本身就带有排序的特性,而且返回的结果集合中包括所有字段。所以通常的执行计划中,即使连接列存在索引,也不会进入到执行计划中。除非进行一些特定列处理。

 

 

SQL> explain plan for select /*+use_merge(tabs,segs)*/segs.segment_name,tabs.table_name from segs,tabs where segs.segment_name=tabs.table_name;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 712326860

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

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

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

|   0 | SELECT STATEMENT       |               |   990 | 37620 |     9  (23)| 00

|   1 |  MERGE JOIN            |               |   990 | 37620 |     9  (23)| 00

|   2 |   SORT JOIN            |               |   968 | 17424 |     4  (25)| 00

|   3 |    INDEX FAST FULL SCAN| IDX_TABS_NAME |   968 | 17424 |     3   (0)| 00

|*  4 |   SORT JOIN            |               |  2267 | 45340 |     5  (20)| 00

|   5 |    INDEX FAST FULL SCAN| IDX_SEGS_NAME |  2267 | 45340 |     4   (0)| 00

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

Predicate Information (identified by operation id):

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

   4 - access("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")

       filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")

18 rows selected

 

 

在对返回结果进行处理的情况下,索引路径会出现的。

 

 

4、结论

 

Merge Sort Join是一种古老经典的排序模型,类似于数据结构时代的合并排序算法。Merge Sort Join引入的最大优势是避免同Nest Loop Join类似的大量随机读现象,但是同时也引入了Sort空间变化的问题。

 

 

随着海量数据处理场景的增多,Merge Sort Join暴露出缺陷的机会越来越多。而Nest Loop Join的大量随机读问题,也是可以通过索引等常规手段加以优化。

 

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

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7753773