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.di