Oracle排序合并连接

为了弥补嵌套循环连接的不足,即在连接数据量比较大时,为了缩减嵌套循环连接中的大量随机读取,以及连接列不存在索引时,可以替代嵌套循环连接。

但是为了实现表连接,它必须要先对两个表中的将要执行连接的行进行排序。虽然这种方式提高了连接效率,但由于排序的存在,增加了连接的代价。如果能够快速的完成排序操作(排序内存空间大小(sort_area_size),并行),那么在处理大数据量时,排序合并连接将是不错的选择。

如果在连接列上有索引,并且利用该索引读取数据,由于索引是有序的,可以消除外部行源的排序操作。

排序合并连接没有嵌套循环连接中驱动表的概念。

  1. SQL> createtable t1 asselect * from dba_objects;
  2. 表已创建。
  3. SQL> createtable t2 asselect * from dba_objects;
  4. 表已创建。
  5. SQL> set autot traceonly
  6. SQL> set linesize 120
  7. SQL> select /*+ use_merge(t1,t2) +*/ t1.object_id,t1.object_name,t2.object_name
  8. 2 from t1,t2
  9. 3 where t1.object_id=t2.object_id;
  10. 已选择50425行。
  11. 执行计划
  12. ----------------------------------------------------------
  13. Plan hash value: 412793182
  14. ------------------------------------------------------------------------------------
  15. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  16. ------------------------------------------------------------------------------------
  17. | 0 | SELECT STATEMENT | | 53430 | 8244K| | 2379 (2)| 00:00:29 |
  18. | 1 | MERGE JOIN | | 53430 | 8244K| | 2379 (2)| 00:00:29 |
  19. | 2 | SORT JOIN | | 53430 | 4122K| 9304K| 1152 (2)| 00:00:14 |
  20. | 3 | TABLE ACCESS FULL| T1 | 53430 | 4122K| | 161 (2)| 00:00:02 |
  21. |* 4 | SORT JOIN | | 57436 | 4431K| 9M| 1227 (2)| 00:00:15 |
  22. | 5 | TABLE ACCESS FULL| T2 | 57436 | 4431K| | 161 (2)| 00:00:02 |
  23. ------------------------------------------------------------------------------------
  24. Predicate Information (identified by operation id):
  25. ---------------------------------------------------
  26. 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  27. filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  28. Note
  29. -----
  30. - dynamic sampling used for this statement
  31. 统计信息
  32. ----------------------------------------------------------
  33. 0 recursive calls
  34. 0 db block gets
  35. 1396 consistent gets
  36. 0 physical reads
  37. 0 redo size
  38. 3166055 bytes sent via SQL*Net to client
  39. 37356 bytes received via SQL*Net from client
  40. 3363 SQL*Net roundtrips to/from client
  41. 2 sorts (memory)
  42. 0 sorts (disk)
  43. 50425 rows processed
  44. SQL> createindex ind_t1_object_id on t1(object_id);
  45. 索引已创建。
  46. SQL> createindex ind_t2_object_id on t2(object_id);
  47. 索引已创建。
  48. /**由于索引是有序的,可以消除外部行源的排序操作*/
  49. SQL> select /*+ use_merge(t1,t2) +*/ t1.object_id,t1.object_name,t2.object_name
  50. 2 from t1,t2
  51. 3 where t1.object_id=t2.object_id ;
  52. 已选择50425行。
  53. 执行计划
  54. ----------------------------------------------------------
  55. Plan hash value: 3739185171
  56. ---------------------------------------------------------------------------------------------------------
  57. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  58. ---------------------------------------------------------------------------------------------------------
  59. | 0 | SELECT STATEMENT | | 53430 | 8244K| | 2031 (1)| 00:00:25 |
  60. | 1 | MERGE JOIN | | 53430 | 8244K| | 2031 (1)| 00:00:25 |
  61. | 2 | TABLE ACCESS BYINDEX ROWID| T2 | 57436 | 4431K| | 879 (1)| 00:00:11 |
  62. | 3 | INDEXFULL SCAN | IND_T2_OBJECT_ID | 57436 | | | 125 (2)| 00:00:02 |
  63. |* 4 | SORT JOIN | | 53430 | 4122K| 9304K| 1152 (2)| 00:00:14 |
  64. | 5 | TABLE ACCESS FULL | T1 | 53430 | 4122K| | 161 (2)| 00:00:02 |
  65. ---------------------------------------------------------------------------------------------------------
  66. Predicate Information (identified by operation id):
  67. ---------------------------------------------------
  68. 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  69. filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  70. Note
  71. -----
  72. - dynamic sampling used for this statement
  73. 统计信息
  74. ----------------------------------------------------------
  75. 8 recursive calls
  76. 0 db block gets
  77. 8384 consistent gets
  78. 117 physical reads
  79. 0 redo size
  80. 3166055 bytes sent via SQL*Net to client
  81. 37356 bytes received via SQL*Net from client
  82. 3363 SQL*Net roundtrips to/from client
  83. 3 sorts (memory)
  84. 0 sorts (disk)
  85. 50425 rows processed
  86. SQL> select /*+ use_merge(t1,t2) index(t1 ind_t1_object_id) +*/ t1.object_id,t1.object_name,t2.object_name
  87. 2 from t1,t2
  88. 3 where t1.object_id=t2.object_id ;
  89. 已选择50425行。
  90. 执行计划
  91. ----------------------------------------------------------
  92. Plan hash value: 3856170417
  93. ---------------------------------------------------------------------------------------------------------
  94. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  95. ---------------------------------------------------------------------------------------------------------
  96. | 0 | SELECT STATEMENT | | 53430 | 8244K| | 2107 (1)| 00:00:26 |
  97. | 1 | MERGE JOIN | | 53430 | 8244K| | 2107 (1)| 00:00:26 |
  98. | 2 | TABLE ACCESS BYINDEX ROWID| T1 | 53430 | 4122K| | 881 (1)| 00:00:11 |
  99. | 3 | INDEXFULL SCAN | IND_T1_OBJECT_ID | 53430 | | | 125 (2)| 00:00:02 |
  100. |* 4 | SORT JOIN | | 57436 | 4431K| 9M| 1227 (2)| 00:00:15 |
  101. | 5 | TABLE ACCESS FULL | T2 | 57436 | 4431K| | 161 (2)| 00:00:02 |
  102. ---------------------------------------------------------------------------------------------------------
  103. Predicate Information (identified by operation id):
  104. ---------------------------------------------------
  105. 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  106. filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  107. Note
  108. -----
  109. - dynamic sampling used for this statement
  110. 统计信息
  111. ----------------------------------------------------------
  112. 7 recursive calls
  113. 0 db block gets
  114. 8386 consistent gets
  115. 0 physical reads
  116. 0 redo size
  117. 3166055 bytes sent via SQL*Net to client
  118. 37356 bytes received via SQL*Net from client
  119. 3363 SQL*Net roundtrips to/from client
  120. 3 sorts (memory)
  121. 0 sorts (disk)
  122. 50425 rows processed
  123. SQL> select /*+ use_merge(t1,t2) index(t1 ind_t1_object_id) index(t2 ind_t2_object_id) +*/ t1.object_id,t1.object_name,
  124. 2.object_name
  125. 2 from t1,t2
  126. 3 where t1.object_id=t2.object_id ;
  127. 已选择50425行。
  128. 执行计划
  129. ----------------------------------------------------------
  130. Plan hash value: 3655247094
  131. ----------------------------------------------------------------------------------------------------------
  132. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  133. ----------------------------------------------------------------------------------------------------------
  134. | 0 | SELECT STATEMENT | | 53430 | 8244K| | 2750 (1)| 00:00:34 |
  135. | 1 | MERGE JOIN | | 53430 | 8244K| | 2750 (1)| 00:00:34 |
  136. | 2 | TABLE ACCESS BYINDEX ROWID | T2 | 57436 | 4431K| | 879 (1)| 00:00:11 |
  137. | 3 | INDEXFULL SCAN | IND_T2_OBJECT_ID | 57436 | | | 125 (2)| 00:00:02 |
  138. |* 4 | SORT JOIN | | 53430 | 4122K| 9304K| 1872 (1)| 00:00:23 |
  139. | 5 | TABLE ACCESS BYINDEX ROWID| T1 | 53430 | 4122K| | 881 (1)| 00:00:11 |
  140. | 6 | INDEXFULL SCAN | IND_T1_OBJECT_ID | 53430 | | | 125 (2)| 00:00:02 |
  141. ----------------------------------------------------------------------------------------------------------
  142. Predicate Information (identified by operation id):
  143. ---------------------------------------------------
  144. 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  145. filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  146. Note
  147. -----
  148. - dynamic sampling used for this statement
  149. 统计信息
  150. ----------------------------------------------------------
  151. 7 recursive calls
  152. 0 db block gets
  153. 8551 consistent gets
  154. 0 physical reads
  155. 0 redo size
  156. 3166055 bytes sent via SQL*Net to client
  157. 37356 bytes received via SQL*Net from client
  158. 3363 SQL*Net roundtrips to/from client
  159. 3 sorts (memory)
  160. 0 sorts (disk)
  161. 50425 rows processed

相关推荐