full join语句练习

full join语句练习 需求：将表 A,B,C 合并到一个结果集中 表A如图： [html] N D 1 eeee 3 dddd 5 cccc 7 bbbb 9 aaaa 11 dddd 13 eeee 15 wwww 17 qqqq 19 tttttt 表B如图：[html] N E 5 rrrrrr 4 fffff 3 ssssss 2 jjjjjj 1 kkkkkk 7 uuuuuu 表C如图：[html

full join语句练习

 

需求：将表 A,B,C 合并到一个结果集中

表A如图：

[html] 
N   D  
1   eeee  
3   dddd  
5   cccc  
7   bbbb  
9   aaaa  
11  dddd  
13  eeee  
15  wwww  
17  qqqq  
19  tttttt  
表B如图：
[html] 
N   E  
5   rrrrrr  
4   fffff  
3   ssssss  
2   jjjjjj  
1   kkkkkk  
7   uuuuuu  
表C如图：
[html] 
N   F  
5   oooo  
4   lllll  
3   hhss  
2   ddfj  
1   kdsfkkk  
7   sduuu  
8   ewrtwy  
12  sdgfsd  
22  dfgee  
要得到的结果集如图：

[html] 
N   D   E   F  
1   eeee    kkkkkk  kdsfkkk  
22          dfgee  
11  dddd           
13  eeee           
2       jjjjjj  ddfj  
5   cccc    rrrrrr  oooo  
4       fffff   lllll  
17  qqqq           
8           ewrtwy  
3   dddd    ssssss  hhss  
7   bbbb    uuuuuu  sduuu  
9   aaaa           
15  wwww           
19  tttttt         
12          sdgfsd  

两种思路：
1, full join
语句为：
[html] 
select  nvl(a.n,nvl(b.n,c.n)) as N,  
            a.d,b.e,c.f  
from A full join B on A.N=B.N   
       full join c on b.n=c.n;  

2, 先union all ，再列转行
语句为：
[html] 
select n ,max(case when nn='a' then d end) as d,  
          max(case when nn='b' then d end) as e,  
          max(case when nn='c' then d end) as f  
from (  
  select n,d as d,'a' as nn from a  
  union all  
  select n,e as d,'b' as nn from b  
  union all  
  select n,f as d,'c' as nn from c  
)  
group by n;  

实际的问题是我要将70个左右的窄表连接成一个宽表，full join 几乎都编译不过，为此我对比了下2种情况的执行计划
使用 full join 的语句
[html] 
explain plan for  
select  nvl(a.n,nvl(b.n,c.n)) as N,  
            a.d,b.e,c.f  
from A full join B on A.N=B.N full join c on b.n=c.n;  
select * from table(dbms_xplan.display());  
执行结果为
[html] 
Plan hash value: 2877137913  
   
--------------------------------------------------------------------------------  
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
--------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT        |      |    19 |  1197 |   122   (4)| 00:00:02 |  
|   1 |  VIEW                   |      |    19 |  1197 |   122   (4)| 00:00:02 |  
|   2 |   UNION-ALL             |      |       |       |            |          |  
|*  3 |    HASH JOIN OUTER      |      |    11 |  1342 |    61   (4)| 00:00:01 |  
|   4 |     VIEW                |      |    11 |   825 |    57   (2)| 00:00:01 |  
|   5 |      UNION-ALL          |      |       |       |            |          |  
|*  6 |       HASH JOIN OUTER   |      |    10 |    60 |    29   (4)| 00:00:01 |  
|   7 |        TABLE ACCESS FULL| A    |    10 |    30 |    14   (0)| 00:00:01 |  
|   8 |        TABLE ACCESS FULL| B    |     5 |    15 |    14   (0)| 00:00:01 |  
|*  9 |       HASH JOIN ANTI    |      |     1 |     6 |    29   (4)| 00:00:01 |  
|  10 |        TABLE ACCESS FULL| B    |     5 |    15 |    14   (0)| 00:00:01 |  
|  11 |        TABLE ACCESS FULL| A    |    10 |    30 |    14   (0)| 00:00:01 |  
|  12 |     TABLE ACCESS FULL   | C    |     9 |   423 |     3   (0)| 00:00:01 |  
|* 13 |    HASH JOIN ANTI       |      |     8 |   272 |    61   (4)| 00:00:01 |  
|  14 |     TABLE ACCESS FULL   | C    |     9 |   189 |     3   (0)| 00:00:01 |  
|  15 |     VIEW                |      |    11 |   143 |    57   (2)| 00:00:01 |  
|  16 |      UNION-ALL          |      |       |       |            |          |  
|* 17 |       HASH JOIN OUTER   |      |    10 |    60 |    29   (4)| 00:00:01 |  
|  18 |        TABLE ACCESS FULL| A    |    10 |    30 |    14   (0)| 00:00:01 |  
|  19 |        TABLE ACCESS FULL| B    |     5 |    15 |    14   (0)| 00:00:01 |  
|* 20 |       HASH JOIN ANTI    |      |     1 |     6 |    29   (4)| 00:00:01 |  
|  21 |        TABLE ACCESS FULL| B    |     5 |    15 |    14   (0)| 00:00:01 |  
|  22 |        TABLE ACCESS FULL| A    |    10 |    30 |    14   (0)| 00:00:01 |  
--------------------------------------------------------------------------------  
   
Predicate Information (identified by operation id):  
---------------------------------------------------  
   
   3 - access("B"."N"="C"."N"(+))  
   6 - access("A"."N"="B"."N"(+))  
   9 - access("A"."N"="B"."N")  
  13 - access("B"."N"="C"."N")  
  17 - access("A"."N"="B"."N"(+))  
  20 - access("A"."N"="B"."N")  
   
Note  
-----  
   - dynamic sampling used for this statement  

使用union all 的语句
[html] 
explain plan for  
select n ,max(case when nn='a' then d end) as d,  
          max(case when nn='b' then d end) as e,  
          max(case when nn='c' then d end) as f  
from (  
   select n,d as d,'a' as nn from a  
   union all  
   select n,e as d,'b' as nn from b  
   union all  
   select n,f as d,'c' as nn from c  
)  
group by n;  
select * from table(dbms_xplan.display());  

执行结果为：
[html] 
1   Plan hash value: 1237158055  
2      
3   -----------------------------------------------------------------------------  
4   | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
5   -----------------------------------------------------------------------------  
6   |   0 | SELECT STATEMENT     |      |    24 |   576 |    32   (4)| 00:00:01 |  
7   |   1 |  HASH GROUP BY       |      |    24 |   576 |    32   (4)| 00:00:01 |  
8   |   2 |   VIEW               |      |    24 |   576 |    31   (0)| 00:00:01 |  
9   |   3 |    UNION-ALL         |      |       |       |            |          |  
10  |   4 |     TABLE ACCESS FULL| A    |    10 |    30 |    14   (0)| 00:00:01 |  
11  |   5 |     TABLE ACCESS FULL| B    |     5 |    15 |    14   (0)| 00:00:01 |  
12  |   6 |     TABLE ACCESS FULL| C    |     9 |   189 |     3   (0)| 00:00:01 |  
13  -----------------------------------------------------------------------------  
14     
15  Note  
16  -----  
17     - dynamic sampling used for this statement  

 

对比2种处理方式，union all 的方式明显优于 full join。也可以看出简单的sql语句效率不一定好。