我试图了解内部如何加入工作.以下两个查询的运行方式之间有什么区别？
For example (A) Select * FROM TABLE1 FULL JOIN TABLE2 ON TABLE1.ID = TABLE2.ID FULL JOIN TABLE3 ON TABLE1.ID = TABLE3.ID And (B) Select * FROM TABLE1 FULL JOIN TABLE2 ON TABLE1.ID = TABLE2.ID FULL JOIN TABLE3 ON TABLE2.ID = TABLE3.ID
编辑:我在这里谈论oracle.考虑表2和表3中存在的一些记录但不在表1中,查询A将为该记录提供两行但B仅给出一行.
您的DBMS优化器将确定如何最好地执行查询.通常,这通过"基于成本的优化"来完成,其中考虑了许多不同的查询计划并且选择了最有效的查询计划. 如果您的两个查询在逻辑上完全相同,那么优化器最有可能最终使用相同的查询计划,无论您以何种方式编写它.事实上,如今这将是一个糟糕的优化者,它根据SQL中的这些微小差异产生了不同的查询计划.
但是,完全外连接是另一个问题(至少在Oracle中),因为列的连接方式会影响结果.即2个查询不可互换.
您可以在SQL Plus中使用AUTOTRACE来查看不同的计划:
SQL> select *
2 from t1
3 full join t2 on t2.id = t1.id
4 full join t3 on t3.id = t2.id;
ID ID ID
---------- ---------- ----------
1 1
1 row selected.
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 117 | 29 (11)|
| 1 | VIEW | | 3 | 117 | 29 (11)|
| 2 | UNION-ALL | | | | |
|* 3 | HASH JOIN OUTER | | 2 | 142 | 15 (14)|
| 4 | VIEW | | 2 | 90 | 11 (10)|
| 5 | UNION-ALL | | | | |
|* 6 | HASH JOIN OUTER | | 1 | 91 | 6 (17)|
| 7 | TABLE ACCESS FULL| T1 | 1 | 52 | 2 (0)|
| 8 | TABLE ACCESS FULL| T2 | 1 | 39 | 3 (0)|
|* 9 | HASH JOIN ANTI | | 1 | 26 | 6 (17)|
| 10 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)|
| 11 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)|
| 12 | TABLE ACCESS FULL | T3 | 1 | 26 | 3 (0)|
|* 13 | HASH JOIN ANTI | | 1 | 26 | 15 (14)|
| 14 | TABLE ACCESS FULL | T3 | 1 | 13 | 3 (0)|
| 15 | VIEW | | 2 | 26 | 11 (10)|
| 16 | UNION-ALL | | | | |
|* 17 | HASH JOIN OUTER | | 1 | 39 | 6 (17)|
| 18 | TABLE ACCESS FULL| T1 | 1 | 26 | 2 (0)|
| 19 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)|
|* 20 | HASH JOIN ANTI | | 1 | 26 | 6 (17)|
| 21 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)|
| 22 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)|
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T3"."ID"(+)="T2"."ID")
6 - access("T2"."ID"(+)="T1"."ID")
9 - access("T2"."ID"="T1"."ID")
13 - access("T3"."ID"="T2"."ID")
17 - access("T2"."ID"(+)="T1"."ID")
20 - access("T2"."ID"="T1"."ID")
SQL> select *
2 from t1
3 full join t2 on t2.id = t1.id
4 full join t3 on t3.id = t1.id;
ID ID ID
---------- ---------- ----------
1
1
2 rows selected.
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 117 | 29 (11)|
| 1 | VIEW | | 3 | 117 | 29 (11)|
| 2 | UNION-ALL | | | | |
|* 3 | HASH JOIN OUTER | | 2 | 142 | 15 (14)|
| 4 | VIEW | | 2 | 90 | 11 (10)|
| 5 | UNION-ALL | | | | |
|* 6 | HASH JOIN OUTER | | 1 | 91 | 6 (17)|
| 7 | TABLE ACCESS FULL| T1 | 1 | 52 | 2 (0)|
| 8 | TABLE ACCESS FULL| T2 | 1 | 39 | 3 (0)|
|* 9 | HASH JOIN ANTI | | 1 | 26 | 6 (17)|
| 10 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)|
| 11 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)|
| 12 | TABLE ACCESS FULL | T3 | 1 | 26 | 3 (0)|
|* 13 | HASH JOIN ANTI | | 1 | 26 | 15 (14)|
| 14 | TABLE ACCESS FULL | T3 | 1 | 13 | 3 (0)|
| 15 | VIEW | | 2 | 26 | 11 (10)|
| 16 | UNION-ALL | | | | |
|* 17 | HASH JOIN OUTER | | 1 | 39 | 6 (17)|
| 18 | TABLE ACCESS FULL| T1 | 1 | 26 | 2 (0)|
| 19 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)|
|* 20 | HASH JOIN ANTI | | 1 | 26 | 6 (17)|
| 21 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)|
| 22 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)|
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T3"."ID"(+)="T1"."ID")
6 - access("T2"."ID"(+)="T1"."ID")
9 - access("T2"."ID"="T1"."ID")
13 - access("T3"."ID"="T1"."ID")
17 - access("T2"."ID"(+)="T1"."ID")
20 - access("T2"."ID"="T1"."ID")
实际上,除了Predicate信息之外,查询计划是相同的
你表达了对"内部"的兴趣,然后问了一个说明"语义"的例子.我正在回答语义.
考虑这些表格.
Table1 : 1, 4, 6 Table2 : 2, 4, 5 Table3 : 3, 5, 6
两个示例都首先执行相同的连接,因此我将在此处执行此操作.
FirstResult = T1 FULL JOIN T2 : (T1, T2) (1, null) (4, 4) (6, null) (null, 2) (null, 5)
例子(A)
FirstResult FULL JOIN T3 ON FirstItem : (T1, T2, T3) (1, null, null) (4, 4, null) (6, null, 6) <---- (null, 2, null) (null, 5, null) <---- (null, null, 3)
例子(B)
FirstResult FULL JOIN T3 ON SecondItem : (T1, T2, T3) (1, null, null) (4, 4, null) (6, null, null) <---- (null, 2, null) (null, 5, 5) <---- (null, null, 3)
这将从逻辑上向您显示如何从联接生成结果.
对于"内部",有一种称为查询优化器的东西,它将产生相同的结果 - 但它将使实现选择能够快速进行计算.这些选择包括:
首先访问哪些表
使用索引或表扫描查看表
加入要使用的实现类型(嵌套循环,合并,散列).
另请注意:由于优化器做出了这些选择,并根据它认为最佳的选择更改这些选择 - 结果的顺序可能会发生变化.结果的默认顺序始终是"最简单的".如果您不想要默认排序,则需要在查询中指定排序.
要确切了解优化器将对查询执行的操作(此时,因为它可以改变其想法),您需要查看执行计划.
京公网安备 11010802040832号 | 京ICP备19059560号-6 