oracle 结果的排序与执行计划有关

最新推荐文章于 2025-11-30 16:29:01 发布

最新推荐文章于 2025-11-30 16:29:01 发布 · 92 阅读

文章标签：

本文通过实例演示了Oracle数据库中三种不同的表连接方法：HASH JOIN、MERGE JOIN 和 NESTED LOOP，并分析了它们对查询结果顺序的影响。指出在不使用ORDER BY的情况下，结果顺序可能不可预测。

SQL> --表中原始数据 SQL> select * from user_sd1104; ID PASS NAME ---- ---- ------- 1005 1234 chengzh 1004 1234 hahah 1003 1222 fdfdfdf 1002 1234 v1v2v2 1001 2345 liyi SQL> set autot on exp SQL> --ORACLE根据动态采样执行计划使用HASH JOIN SQL> SELECT u1.id, u1.password, u2.id, u2.password 2 FROM user_sd1104 u1 3 LEFT OUTER JOIN user_sd1104 u2 4 ON u1.password = u2.password; ID PASS ID PASS ---- ---- ---- ---- 1002 1234 1005 1234 1004 1234 1005 1234 1005 1234 1005 1234 1002 1234 1004 1234 1004 1234 1004 1234 1005 1234 1004 1234 1003 1222 1003 1222 1002 1234 1002 1234 1004 1234 1002 1234 1005 1234 1002 1234 1001 2345 1001 2345 已选择11行。执行计划 ---------------------------------------------------------- Plan hash value: 2237943440 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 192 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN OUTER | | 8 | 192 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL| USER_SD1104 | 5 | 60 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| USER_SD1104 | 5 | 60 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("U1"."PASSWORD"="U2"."PASSWORD"(+)) Note ----- - dynamic sampling used for this statement SQL> --强制执行计划使用NEST LOOP SQL> SELECT/*+use_nl(u1,u2)*/ u1.id, u1.password, u2.id, u2.password 2 FROM user_sd1104 u1 3 LEFT OUTER JOIN user_sd1104 u2 4 ON u1.password = u2.password; ID PASS ID PASS ---- ---- ---- ---- 1005 1234 1005 1234 1005 1234 1004 1234 1005 1234 1002 1234 1004 1234 1005 1234 1004 1234 1004 1234 1004 1234 1002 1234 1003 1222 1003 1222 1002 1234 1005 1234 1002 1234 1004 1234 1002 1234 1002 1234 1001 2345 1001 2345 已选择11行。执行计划 ---------------------------------------------------------- Plan hash value: 769104428 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 192 | 9 (0)| 00:00:01 | | 1 | NESTED LOOPS OUTER| | 8 | 192 | 9 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| USER_SD1104 | 5 | 60 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| USER_SD1104 | 2 | 24 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("U1"."PASSWORD"="U2"."PASSWORD"(+)) Note ----- - dynamic sampling used for this statement SQL> --强制执行计划使用merg join SQL> SELECT/*+use_merge(u1,u2)*/ u1.id, u1.password, u2.id, u2.password 2 FROM user_sd1104 u1 3 LEFT OUTER JOIN user_sd1104 u2 4 ON u1.password = u2.password; ID PASS ID PASS ---- ---- ---- ---- 1003 1222 1003 1222 1004 1234 1004 1234 1004 1234 1002 1234 1004 1234 1005 1234 1002 1234 1004 1234 1002 1234 1002 1234 1002 1234 1005 1234 1005 1234 1004 1234 1005 1234 1002 1234 1005 1234 1005 1234 1001 2345 1001 2345 已选择11行。执行计划 ---------------------------------------------------------- Plan hash value: 1751357394 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 192 | 8 (25)| 00:00:01 | | 1 | MERGE JOIN OUTER | | 8 | 192 | 8 (25)| 00:00:01 | | 2 | SORT JOIN | | 5 | 60 | 4 (25)| 00:00:01 | | 3 | TABLE ACCESS FULL| USER_SD1104 | 5 | 60 | 3 (0)| 00:00:01 | |* 4 | SORT JOIN | | 5 | 60 | 4 (25)| 00:00:01 | | 5 | TABLE ACCESS FULL| USER_SD1104 | 5 | 60 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("U1"."PASSWORD"="U2"."PASSWORD"(+)) filter("U1"."PASSWORD"="U2"."PASSWORD"(+)) Note ----- - dynamic sampling used for this statement SQL> /* 总结：从表面上看，NEST LOOP执行计划(第二种)最可能是意料中的顺序。因此两表连接，结果的最终顺序是与执行计划有关的。并且在不强制使用采用某种连接方式之前，你无法确定oracle采用什么执行计划。所以数据的顺序可能是无法确定的。因此，如果你需要按某种顺序输出最好增加order by来排序。从另外一个方面来说，是否使用HINT来强制使用某种连接方式来确定顺序就好呢？回答是否定的，因为这样可能不是SQL最优的执行方式。不要捡了芝麻丢掉西瓜！ */

原帖：http://topic.youkuaiyun.com/u/20110609/23/6aa6389d-699d-42ff-be97-bee1d82836d8.html?98354

HASH JOIN ,MERGE JOIN ,NESTED LOOP:

[1].http://blogold.chinaunix.net/u1/46451/showart_415529.html

Oracle中的Hash Join祥解:

[1].http://www.hellodba.com/reader.php?ID=144&lang=cn

[2].http://www.remote-dba.net/pl_sql/t_oracle_hash_joins.htm

Using hints with join methods:

[1].http://guyharrison.squarespace.com/blog/2009/8/30/using-hints-with-join-methods.html