1、准备数据:
create table a(a_id number,a_name varchar2(20));
create table b(b_id number,b_name varchar2(20));
create table c(c_id number,c_name varchar2(20));
insert into a values(1,'cupid');
insert into a values(2,'cupid');
insert into a values(3,'cupid');
insert into b values(1,'cupid');
insert into b values(2,'cupid');
insert into b values(4,'cupid');
insert into c values(1,'cupid');
insert into c values(3,'cupid');
insert into c values(4,'cupid');
commit;
2、模拟修改前:
select * from a full join b on a.a_id=b.b_id full join c on a.a_id=c.c_id;
结果:
A_ID A_NAME B_ID B_NAME C_ID C_NAME
1 1 cupid 1 cupid 1 cupid
2 2 cupid 2 cupid
3 4 cupid
4 3 cupid 3 cupid 3 cupid
5 4 cupid
6 4 cupid
出现的问题是第3条数据和第5条记录重复;
3、解决办法可以使用:
select * from a full join b on a.a_id=b.b_id full join c on a.a_id=c.c_id or b.b_id=c.c_id
A_ID A_NAME B_ID B_NAME C_ID C_NAME
1 1 cupid 1 cupid 1 cupid
2 2 cupid 2 cupid
3 4 cupid 4 cupid
4 3 cupid 3 cupid 3 cupid
5 4 cupid 4 cupid
或者:
select * from a full join b on a.a_id=b.b_id full join c on nvl(a.a_id,b.b_id)=c.c_id
4、问题:哪个效率高?
解答:
使用or的执行计划:
执行计划
----------------------------------------------------------
Plan hash value: 1706957878
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 525 | 38 (6)| 00:00:01 |
| 1 | VIEW | | 7 | 525 | 38 (6)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS OUTER | | 5 | 375 | 22 (5)| 00:00:01 |
| 4 | VIEW | VW_FOJ_0 | 5 | 250 | 7 (15)| 00:00:01 |
|* 5 | HASH JOIN FULL OUTER | | 5 | 250 | 7 (15)| 00:00:01 |
| 6 | TABLE ACCESS FULL | A | 3 | 75 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | B | 5 | 125 | 3 (0)| 00:00:01 |
| 8 | VIEW | | 1 | 25 | 3 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | C | 1 | 25 | 3 (0)| 00:00:01 |
|* 10 | HASH JOIN ANTI | | 2 | 76 | 17 (12)| 00:00:01 |
| 11 | TABLE ACCESS FULL | C | 3 | 75 | 3 (0)| 00:00:01 |
| 12 | VIEW | VW_SQ_1 | 10 | 130 | 13 (8)| 00:00:01 |
| 13 | UNION-ALL | | | | | |
| 14 | VIEW | VW_FOJ_1 | 5 | 65 | 7 (15)| 00:00:01 |
|* 15 | HASH JOIN FULL OUTER| | 5 | 130 | 7 (15)| 00:00:01 |
| 16 | TABLE ACCESS FULL | A | 3 | 39 | 3 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | B | 5 | 65 | 3 (0)| 00:00:01 |
| 18 | VIEW | VW_FOJ_1 | 5 | 65 | 7 (15)| 00:00:01 |
|* 19 | HASH JOIN FULL OUTER| | 5 | 130 | 7 (15)| 00:00:01 |
| 20 | TABLE ACCESS FULL | A | 3 | 39 | 3 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | B | 5 | 65 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."A_ID"="B"."B_ID")
9 - filter("A"."A_ID"="C"."C_ID" OR "B"."B_ID"="C"."C_ID")
10 - access("VW_COL_1"="C"."C_ID")
15 - access("A"."A_ID"="B"."B_ID")
19 - access("A"."A_ID"="B"."B_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
使用nvl的执行计划:
执行计划
----------------------------------------------------------
Plan hash value: 2332716859
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 975 | 10 (10)| 00:00:01 |
| 1 | VIEW | VW_FOJ_0 | 13 | 975 | 10 (10)| 00:00:01 |
|* 2 | HASH JOIN FULL OUTER | | 13 | 975 | 10 (10)| 00:00:01 |
| 3 | TABLE ACCESS FULL | C | 3 | 75 | 3 (0)| 00:00:01 |
| 4 | VIEW | VW_FOJ_1 | 5 | 250 | 7 (15)| 00:00:01 |
|* 5 | HASH JOIN FULL OUTER| | 5 | 250 | 7 (15)| 00:00:01 |
| 6 | TABLE ACCESS FULL | A | 3 | 75 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | B | 5 | 125 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C"."C_ID"=NVL("A"."A_ID","B"."B_ID"))
5 - access("A"."A_ID"="B"."B_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
5、结论:
目前测试环境下nvl效率好一点,但是实际应用可能会因为索引和表的数据量有差别,所以需要两种办法具体的执行计划看下。
create table a(a_id number,a_name varchar2(20));
create table b(b_id number,b_name varchar2(20));
create table c(c_id number,c_name varchar2(20));
insert into a values(1,'cupid');
insert into a values(2,'cupid');
insert into a values(3,'cupid');
insert into b values(1,'cupid');
insert into b values(2,'cupid');
insert into b values(4,'cupid');
insert into c values(1,'cupid');
insert into c values(3,'cupid');
insert into c values(4,'cupid');
commit;
2、模拟修改前:
select * from a full join b on a.a_id=b.b_id full join c on a.a_id=c.c_id;
结果:
A_ID A_NAME B_ID B_NAME C_ID C_NAME
1 1 cupid 1 cupid 1 cupid
2 2 cupid 2 cupid
3 4 cupid
4 3 cupid 3 cupid 3 cupid
5 4 cupid
6 4 cupid
出现的问题是第3条数据和第5条记录重复;
3、解决办法可以使用:
select * from a full join b on a.a_id=b.b_id full join c on a.a_id=c.c_id or b.b_id=c.c_id
A_ID A_NAME B_ID B_NAME C_ID C_NAME
1 1 cupid 1 cupid 1 cupid
2 2 cupid 2 cupid
3 4 cupid 4 cupid
4 3 cupid 3 cupid 3 cupid
5 4 cupid 4 cupid
或者:
select * from a full join b on a.a_id=b.b_id full join c on nvl(a.a_id,b.b_id)=c.c_id
4、问题:哪个效率高?
解答:
使用or的执行计划:
执行计划
----------------------------------------------------------
Plan hash value: 1706957878
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 525 | 38 (6)| 00:00:01 |
| 1 | VIEW | | 7 | 525 | 38 (6)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS OUTER | | 5 | 375 | 22 (5)| 00:00:01 |
| 4 | VIEW | VW_FOJ_0 | 5 | 250 | 7 (15)| 00:00:01 |
|* 5 | HASH JOIN FULL OUTER | | 5 | 250 | 7 (15)| 00:00:01 |
| 6 | TABLE ACCESS FULL | A | 3 | 75 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | B | 5 | 125 | 3 (0)| 00:00:01 |
| 8 | VIEW | | 1 | 25 | 3 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | C | 1 | 25 | 3 (0)| 00:00:01 |
|* 10 | HASH JOIN ANTI | | 2 | 76 | 17 (12)| 00:00:01 |
| 11 | TABLE ACCESS FULL | C | 3 | 75 | 3 (0)| 00:00:01 |
| 12 | VIEW | VW_SQ_1 | 10 | 130 | 13 (8)| 00:00:01 |
| 13 | UNION-ALL | | | | | |
| 14 | VIEW | VW_FOJ_1 | 5 | 65 | 7 (15)| 00:00:01 |
|* 15 | HASH JOIN FULL OUTER| | 5 | 130 | 7 (15)| 00:00:01 |
| 16 | TABLE ACCESS FULL | A | 3 | 39 | 3 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | B | 5 | 65 | 3 (0)| 00:00:01 |
| 18 | VIEW | VW_FOJ_1 | 5 | 65 | 7 (15)| 00:00:01 |
|* 19 | HASH JOIN FULL OUTER| | 5 | 130 | 7 (15)| 00:00:01 |
| 20 | TABLE ACCESS FULL | A | 3 | 39 | 3 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | B | 5 | 65 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."A_ID"="B"."B_ID")
9 - filter("A"."A_ID"="C"."C_ID" OR "B"."B_ID"="C"."C_ID")
10 - access("VW_COL_1"="C"."C_ID")
15 - access("A"."A_ID"="B"."B_ID")
19 - access("A"."A_ID"="B"."B_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
使用nvl的执行计划:
执行计划
----------------------------------------------------------
Plan hash value: 2332716859
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 975 | 10 (10)| 00:00:01 |
| 1 | VIEW | VW_FOJ_0 | 13 | 975 | 10 (10)| 00:00:01 |
|* 2 | HASH JOIN FULL OUTER | | 13 | 975 | 10 (10)| 00:00:01 |
| 3 | TABLE ACCESS FULL | C | 3 | 75 | 3 (0)| 00:00:01 |
| 4 | VIEW | VW_FOJ_1 | 5 | 250 | 7 (15)| 00:00:01 |
|* 5 | HASH JOIN FULL OUTER| | 5 | 250 | 7 (15)| 00:00:01 |
| 6 | TABLE ACCESS FULL | A | 3 | 75 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | B | 5 | 125 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C"."C_ID"=NVL("A"."A_ID","B"."B_ID"))
5 - access("A"."A_ID"="B"."B_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
5、结论:
目前测试环境下nvl效率好一点,但是实际应用可能会因为索引和表的数据量有差别,所以需要两种办法具体的执行计划看下。