一个自连接-左外连的问题

Question

（1）原表
SQL> select * from user_sd1104;        ID PASS NAME
---------- ---- --------------------
      1005 1234 chengzh
      1004 1234 hahah
      1003 1222 fdfdfdf
      1002 1234 v1v2v2
      1001 2345 liyi（2）左外之后的结果集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请解释下结果的顺序，为什么左边不是按照表的原始顺序开始排列的？谢谢

Answer 1

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 statementSQL> --强制执行计划使用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 statementSQL> 

Answer 2

总结：
从表面上看，NEST LOOP执行计划(第二种)最可能是意料中的顺序。因此两表连接，结果的最终顺序是与执行计划有关的。
并且在不强制使用采用某种连接方式之前，你无法确定oracle采用什么执行计划。
所以数据的顺序可能是无法确定的。因此，如果你需要按某种顺序输出
最好增加order by来排序。从另外一个方面来说，是否使用HINT来强制使用某种连接方式来确定顺序就好呢？
回答是否定的，因为这样可能不是SQL最优的执行方式。不要捡了芝麻丢掉西瓜！

Answer 3

数据库中 select 后的结果集并不是保证次序的，除非是隐含了有排序操作，比如 distinct, group by 之类的，这给数据库厂商自己决定的空间，厂商可能根据自己的算法来提高性能。