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语句效率不一定好。