左右连接right join 和 (+)= 区别

Question

昨天运行了一个SQL语句，大概是这样的
table1 共76行
table2 共92行当我用 right join 做右连接时共得到92行数据  耗时13秒以上（多次运行都是这样）；
当我用 (+)= 做右连接时 共得到92行数据，耗时0.357秒左右（多次运行都是这样）；我在网上查了一下，说这只是语法不通，可为什么执行效率差距这么大呢？
Oracle9i数据库，表没做任何索引。
希望朋友给个回答。我原SQL如下：
 select area_name,nvl(user_num,0) user_num,
           nvl(wdk_num,0) wdk_num,
           nvl(wmb_num,0) wmb_num
        from ( select a.area_code,user_num,wdk_num,wmb_num
          from ( select area_code,sum(user_num) user_num from rsphonewuportresource where bureau_id=100602 group by area_code) a
        left join (select area_code,sum(decode(type,'A',1,0)) wdk_num,sum(decode(type,'B',1,0)) wmb_num from rsphonewuportuser where bureau_id=100602 group by area_code) b
         on a.area_code=b.area_code) aa 
right join 
     (select a.area_code ,area_name from rsphoneareamap a inner join rsphoneareabureaumap b on a.area_code=b.area_code and b.bureau_code=100602        ) bb
     on aa.area_code=bb.area_code 
     order by aa.area_code;     耗时13秒以上
这里的aa就是上面的table1表，共76行，查询耗时0.354秒左右，bb就是上面的table2表，共92行，查询耗时0.188秒左右；
改为        select area_name,nvl(user_num,0) user_num,
           nvl(wdk_num,0) wdk_num,
           nvl(wmb_num,0) wmb_num
        from ( select a.area_code,user_num,wdk_num,wmb_num
          from ( select area_code,sum(user_num) user_num from rsphonewuportresource where bureau_id=100602 group by area_code) a
        left join (select area_code,sum(decode(type,'A',1,0)) wdk_num,sum(decode(type,'B',1,0)) wmb_num from rsphonewuportuser where bureau_id=100602 group by area_code) b
         on a.area_code=b.area_code) aa 
, 
     (select a.area_code ,area_name from rsphoneareamap a inner join rsphoneareabureaumap b on a.area_code=b.area_code and b.bureau_code=100602        ) bb
     where aa.area_code(+)=bb.area_code 
     order by aa.area_code;
耗时0.357秒左右

Answer 1

可能是用作SQL的基本表不同吧
把第一个SQL(13秒那个)改成如下试试:
 select area_name,nvl(user_num,0) user_num, 
           nvl(wdk_num,0) wdk_num, 
           nvl(wmb_num,0) wmb_num 
        from 
     (select a.area_code ,area_name from rsphoneareamap a inner join rsphoneareabureaumap b on a.area_code=b.area_code and b.bureau_code=100602         ) [color=#FF000000]bb[/color] 
[color=#FF000000]left join[/color]
( select a.area_code,user_num,wdk_num,wmb_num 
          from ( select area_code,sum(user_num) user_num from rsphonewuportresource where bureau_id=100602 group by area_code) a 
        left join (select area_code,sum(decode(type,'A',1,0)) wdk_num,sum(decode(type,'B',1,0)) wmb_num from rsphonewuportuser where bureau_id=100602 group by area_code) b 
         on a.area_code=b.area_code) [color=#FF000000]aa  [/color]
     on aa.area_code=bb.area_code  
     order by aa.area_code;

Answer 2

可能是用作SQL的基本表不同吧
把第一个SQL(13秒那个)改成如下试试:
 select area_name,nvl(user_num,0) user_num, 
           nvl(wdk_num,0) wdk_num, 
           nvl(wmb_num,0) wmb_num 
        from 
     (select a.area_code ,area_name from rsphoneareamap a inner join rsphoneareabureaumap b on a.area_code=b.area_code and b.bureau_code=100602         ) bb 
left join
( select a.area_code,user_num,wdk_num,wmb_num 
          from ( select area_code,sum(user_num) user_num from rsphonewuportresource where bureau_id=100602 group by area_code) a 
        left join (select area_code,sum(decode(type,'A',1,0)) wdk_num,sum(decode(type,'B',1,0)) wmb_num from rsphonewuportuser where bureau_id=100602 group by area_code) b 
         on a.area_code=b.area_code) aa  
     on aa.area_code=bb.area_code  
     order by aa.area_code;

Answer 3

DragonBill 
我试过了，还是不可以，在13s以上。

Answer 4

SELECT STATEMENT, GOAL = CHOOSE Cost=2134 Cardinality=2772 Bytes=260568
 SORT ORDER BY Cost=2134 Cardinality=2772 Bytes=260568
  HASH JOIN OUTER Cost=2085 Cardinality=2772 Bytes=260568
   HASH JOIN Cost=5 Cardinality=11 Bytes=528
    TABLE ACCESS FULL Object owner=OWNSYS Object name=RSPHONEAREABUREAUMAP Cost=2 Cardinality=11 Bytes=154
    TABLE ACCESS FULL Object owner=OWNSYS Object name=RSPHONEAREAMAP Cost=2 Cardinality=1062 Bytes=36108
   VIEW Object owner=OWNSYS Cost=2079 Cardinality=261 Bytes=12006
    NESTED LOOPS OUTER Cost=2079 Cardinality=261 Bytes=12006
     VIEW Object owner=OWNSYS Cost=6 Cardinality=29 Bytes=580
      SORT GROUP BY Cost=6 Cardinality=29 Bytes=609
       TABLE ACCESS FULL Object owner=OWNSYS Object name=RSPHONEWUPORTRESOURCE Cost=4 Cardinality=29 Bytes=609
     VIEW Object owner=SYS Cardinality=9 Bytes=234
      SORT GROUP BY Cost=72 Cardinality=9 Bytes=189
       TABLE ACCESS FULL Object owner=OWNSYS Object name=RSPHONEWUPORTUSER Cost=70 Cardinality=9 Bytes=189前一个的执行计划
SELECT STATEMENT, GOAL = CHOOSE Cost=88 Cardinality=11 Bytes=1034
 SORT ORDER BY Cost=88 Cardinality=11 Bytes=1034
  HASH JOIN OUTER Cost=86 Cardinality=11 Bytes=1034
   HASH JOIN Cost=5 Cardinality=11 Bytes=528
    TABLE ACCESS FULL Object owner=OWNSYS Object name=RSPHONEAREABUREAUMAP Cost=2 Cardinality=11 Bytes=154
    TABLE ACCESS FULL Object owner=OWNSYS Object name=RSPHONEAREAMAP Cost=2 Cardinality=1062 Bytes=36108
   VIEW Object owner=OWNSYS Cost=80 Cardinality=34 Bytes=1564
    HASH JOIN OUTER Cost=80 Cardinality=34 Bytes=1802
     VIEW Object owner=OWNSYS Cost=6 Cardinality=29 Bytes=580
      SORT GROUP BY Cost=6 Cardinality=29 Bytes=609
       TABLE ACCESS FULL Object owner=OWNSYS Object name=RSPHONEWUPORTRESOURCE Cost=4 Cardinality=29 Bytes=609
     VIEW Object owner=OWNSYS Cost=74 Cardinality=920 Bytes=30360
      SORT GROUP BY Cost=74 Cardinality=920 Bytes=19320
       TABLE ACCESS FULL Object owner=OWNSYS Object name=RSPHONEWUPORTUSER Cost=70 Cardinality=920 Bytes=19320
后一个的

Answer 5

right join 是建立虚拟表，相当与视图。然后在从这个虚拟表取得数据。
（+）=是直接从数据表中返回结果。当然是（+）=快了

Answer 6

第一个是 NESTED LOOPS
后一个是 HASH JOIN差别就在这里, 你把两表的结构贴出来吧, 我测试一下

Answer 7

DragonBill是你说的这么一回事，但我不知道为什么。我把涉及到的四个表结构发在下面，麻烦了。
talbe_name              colum_name      data_type              data_length
RSPHONEWUPORTRESOURCE AREA_CODE VARCHAR2 10
RSPHONEWUPORTRESOURCE BUREAU_ID VARCHAR2 10
RSPHONEWUPORTRESOURCE LOCAL_CODE VARCHAR2 10
RSPHONEWUPORTRESOURCE USER_NUM VARCHAR2 10
RSPHONEWUPORTRESOURCE WDK_NUM VARCHAR2 10
RSPHONEWUPORTRESOURCE WMB_NUM VARCHAR2 10
RSPHONEWUPORTRESOURCE UPDATE_FLAG VARCHAR2 5
RSPHONEWUPORTRESOURCE INSERT_FLAG VARCHAR2 5
RSPHONEWUPORTRESOURCE DELETE_FLAG VARCHAR2 5
RSPHONEWUPORTRESOURCE UPDATE_TIME VARCHAR2 30
----------
RSPHONEWUPORTUSER PHONE_NUM VARCHAR2 10
RSPHONEWUPORTUSER TYPE VARCHAR2 10
RSPHONEWUPORTUSER AREA_CODE VARCHAR2 10
RSPHONEWUPORTUSER BUREAU_ID VARCHAR2 10
RSPHONEWUPORTUSER LOCAL_CODE VARCHAR2 10
RSPHONEWUPORTUSER UPDATE_FLAG VARCHAR2 5
RSPHONEWUPORTUSER INSERT_FLAG VARCHAR2 5
RSPHONEWUPORTUSER DELETE_FLAG VARCHAR2 5
RSPHONEWUPORTUSER UPDATE_TIME VARCHAR2 30
-------
RSPHONEAREAMAP AREA_CODE VARCHAR2 10
RSPHONEAREAMAP AREA_NAME VARCHAR2 50
RSPHONEAREAMAP AREA_DESC VARCHAR2 30
------
RSPHONEAREABUREAUMAP BUREAU_CODE VARCHAR2 10
RSPHONEAREABUREAUMAP BUREAU_DESC VARCHAR2 30
RSPHONEAREABUREAUMAP AREA_CODE VARCHAR2 10
RSPHONEAREABUREAUMAP BUREAU_NAME VARCHAR2 50
----

Answer 8

把 select area_name,nvl(user_num,0) user_num, 
           nvl(wdk_num,0) wdk_num, 
           nvl(wmb_num,0) wmb_num 
        from ( select a.area_code,user_num,wdk_num,wmb_num 
          from ( select area_code,sum(user_num) user_num from rsphonewuportresource where bureau_id=100602 group by area_code) a 
        left join (select area_code,sum(decode(type,'A',1,0)) wdk_num,sum(decode(type,'B',1,0)) wmb_num from rsphonewuportuser where bureau_id=100602 group by area_code) b 
         on a.area_code=b.area_code) aa改成SELECT area_code,
     sum(user_num) user_num,
     sum(wdk_num) wdk_num,
     sum(wmb_num) wmb_num
   FROM
    (SELECT a.area_code area_code,
       user_num,
       decode(type,    'A',    1,    0) wdk_num,
       decode(type,    'B',    1,    0) wmb_num       
FROM rsphonewuportresource a
LEFT JOIN rsphonewuportuser b on a.area_code = b.area_code
WHERE a.bureau_id = 100602 and b.bureau_id = 100602)
group by area_code就行了.原因是:Oracle优化时, 会自动认为之前的那种SQL采用Nested Loops效率高.

Answer 9

select area_code,sum(user_num) user_num from rsphonewuportresource where bureau_id=100602 group by area_code) a 
        left join (select area_code,sum(decode(type,'A',1,0)) wdk_num,sum(decode(type,'B',1,0)) wmb_num from rsphonewuportuser where bureau_id=100602 group by area_code) b 
         on a.area_code=b.area_code整个SQL解析如下:
    SQL Engine先对 rsphonewuportresource 中满足条件的Record GROUP BY, 形成子查询A
    接着在 rsphonewuportuser 中查找 area_code 等于 A.area_code 并且 bureau_id 等于 100602的
    然后对符合条件的 rsphonewuportuser 中的记录GROUP BY, 形成子查询B
    最后以A为基本表, 与B Nested Loops 形成 temp subQuery aa

Answer 10

我这边单独运行
select area_code,sum(user_num) user_num from rsphonewuportresource where bureau_id=100602 group by area_code) a 
        left join (select area_code,sum(decode(type,'A',1,0)) wdk_num,sum(decode(type,'B',1,0)) wmb_num from rsphonewuportuser where bureau_id=100602 group by area_code) b 
         on a.area_code=b.area_code的执行计划显示的不是使用Nested Loops 
而是：SELECT STATEMENT, GOAL = CHOOSE Cost=8 Cardinality=1 Bytes=53
 HASH JOIN OUTER Cost=8 Cardinality=1 Bytes=53
  VIEW Object owner=OWNSYS Cost=4 Cardinality=1 Bytes=20
   SORT GROUP BY Cost=4 Cardinality=1 Bytes=21
    TABLE ACCESS FULL Object owner=OWNSYS Object name=RSPHONEWUPORTRESOURCE Cost=2 Cardinality=1 Bytes=21
  VIEW Object owner=OWNSYS Cost=4 Cardinality=1 Bytes=33
   SORT GROUP BY Cost=4 Cardinality=1 Bytes=21
    TABLE ACCESS FULL Object owner=OWNSYS Object name=RSPHONEWUPORTUSER Cost=2 Cardinality=1 Bytes=21

Answer 11

应该是查询优化器分析SQL成本时作出的选择(它认为NESTED LOOPS是较之HASH JOIN要好的选择)
实际上, 你在 right join的那个SQL中把select area_code,sum(user_num) user_num from rsphonewuportresource where bureau_id=100602 group by area_code) a 
        left join (select area_code,sum(decode(type,'A',1,0)) wdk_num,sum(decode(type,'B',1,0)) wmb_num from rsphonewuportuser where bureau_id=100602 group by area_code) b 
         on a.area_code=b.area_code改成select area_code,sum(user_num) user_num from rsphonewuportresource where bureau_id=100602 group by area_code) a 
        ,(select area_code,sum(decode(type,'A',1,0)) wdk_num,sum(decode(type,'B',1,0)) wmb_num from rsphonewuportuser where bureau_id=100602 group by area_code) b 
         where a.area_code=b.area_code(+)其分析结果又将变成HASH JOIN

Answer 12

DragonBill 是你说的这么一回事，我改了，确实分析结果又将变成HASH JOIN。
“应该是查询优化器分析SQL成本时作出的选择(它认为NESTED LOOPS是较之HASH JOIN要好的选择) ”这句话我能理解。
那为什么用（+）=做右连接时，查询优化器分析SQL成本时未作出这样的选择(它认为NESTED LOOPS是较之HASH JOIN要好的选择)，而使用right join的时候这么认为？是不是可以理解为（+）=和right join还是有区别的？有区别，是不是9楼chliang315说的那样？
right join 是建立虚拟表，相当与视图。然后在从这个虚拟表取得数据。 
（+）=是直接从数据表中返回结果。当然是（+）=快了谢谢。
也希望9楼的chliang315朋友给个确认，谢谢！

Answer 13

没区别, 只是 Oracle SQL 查询器 在分析SQL语句时作出的不同解释

Answer 14

也可能是 SQL 查询器 在分析SQL时根据选择的驱动表不同作出了不同的分析判断吧
只是这样推测, 不敢肯定.找时间再确认一下:)