full join 太费时间了

最新推荐文章于 2024-08-13 23:06:11 发布

shuishouhcd

最新推荐文章于 2024-08-13 23:06:11 发布

阅读量3.6k

点赞数 2

分类专栏： oracle 文章标签： join access table c sql

本文链接：https://blog.csdn.net/shuishouhcd/article/details/7579795

版权

oracle 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

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

表A如图：

N	D
1	eeee
3	dddd
5	cccc
7	bbbb
9	aaaa
11	dddd
13	eeee
15	wwww
17	qqqq
19	tttttt

表B如图：

N	E
5	rrrrrr
4	fffff
3	ssssss
2	jjjjjj
1	kkkkkk
7	uuuuuu

表C如图：

N	F
5	oooo
4	lllll
3	hhss
2	ddfj
1	kdsfkkk
7	sduuu
8	ewrtwy
12	sdgfsd
22	dfgee

要得到的结果集如图：

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

语句为：

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 ，再列转行

语句为：

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 的语句

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());

执行结果为

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 的语句

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());

执行结果为：

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