full join的一个小测试

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            4cupid
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_IDA_NAME B_IDB_NAME C_IDC_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效率好一点，但是实际应用可能会因为索引和表的数据量有差别，所以需要两种办法具体的执行计划看下。