Fulljoin statement exercise

Fulljoin statement exercise requirement: Merge tables A, B, and C into A result set Table A [html] nd1eeee3dddddd5cccc7bbbb9aaaa11dddd13eeee15ww17qqqq19tttt table B [html] Character Table C [html

Full join statement exercise requirements: Convert tables A, B, C merge to A result set Table A [html] n d 1 eeee 3 dddd 5 cccc 7 bbbb 9 aaaa 11 dddd 13 eeee 15 wwww 17 qqqq 19 tttttttt table B [html] N E 5 rrrrrr 4 fffff 3 ssssss 2 jjjjjj 1 kkkkkkk 7 uuuuuu Table C [html

Full join statement exercise

Requirement: Merge tables A, B, and C into A result set.

Table

[Html] n d 1 eeee 3 dddd 5 cccc 7 bbbb 9 aaaa 11 dddd 13 eeee 15 wwww 17 qqqq 19 tttttt table B [html] N E 5 rrrrrr 4 fffff 3 ssssss 2 jjjjjj 1 kkkkkk 7 uuuuuu Table C [html] n f 5 oooo 4 lllll 3 hhss 2 ddfj 1 kdsfkkk 7 sduuu 8 ewrtwy 12 sdgfsd 22 dfgee expected result set [html] N D E F 1 eeee 127kdsfkkk 22 dfgee 11 dddd 13 eeee 2 127ddfj 5 cccc rrrrrr oooo 4 fffff lllll 17 qqqq 8 1273 dddd ssssss hhss 7 bbbb 417sduuu 9 aaaa 15 wwww 19 tttttttt 12 two sdgfsd ideas: 1. The full join statement is: [html] select nvl (. n, nvl (B. n, c. n) as N,. d, B. e, c. f from A full join B on. N = B. N full join c on B. n = c. n; 2, union all first, and then column-to-row statement: [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; the actual problem is that I want to connect 70 or so narrow tables into a wide table, and full join can hardly be compiled, for this reason, I compared the execution plan in the following two cases using the full join statement [html] explain plan for select nvl (. n, nvl (B. n, c. n) as N,. d, B. e, c. f from A full join B on. N = B. N full join c on B. n = c. n; select * from table (dbms_xplan.display (); execution result: [html] Plan hash value: 2877137913 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 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 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 3-access ("B ". "N" = "C ". "N" (+) 6-access ("". "N" = "B ". "N" (+) 9-access ("". "N" = "B ". "N") 13-access ("B ". "N" = "C ". "N") 17-access ("". "N" = "B ". "N" (+) 20-access ("". "N" = "B ". "N") Note ------dynamic sampling used for this statement use the union all statement [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 (); execution result: [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 minutes 14 15 Note 16 ----- 17-dynamic sampling used for this statement

Compared with the two processing methods, the union all method is significantly better than the full join method. We can also see that the efficiency of simple SQL statements is not necessarily good.