下面 SQL 有 OR 关联条件。

    1. SELECT A.CONTRACT_ID, B.BORROWER_ID
    2.   FROM blfct.bl_rtl_con_overdue_fact A
    3.   LEFT JOIN BLpub.Bl_Contract_Dim B ON A.DEALER_ID = B.DEALER_ID
    4.                                     OR A.OVERDUE_DD = B.Overdue_Dd
    5.  WHERE A.ETL_DATE BETWEEN DATE '2016-12-19' AND DATE '2016-12-20';

    执行计划如下。

    1. Plan hash value: 121649910
    3. ------------------------------------------------------------------------------------
    4. | Id  | Operation           | Name                    | Rows  | Bytes | Cost (%CPU)|
    5. ------------------------------------------------------------------------------------
    6. |   0 | SELECT STATEMENT    |                         |   163M|  5469M|  4421M  (1)|
    7. |   1 |  NESTED LOOPS OUTER |                         |   163M|  5469M|  4421M  (1)|
    8. |*  2 |   TABLE ACCESS FULL | BL_RTL_CON_OVERDUE_FACT |   181K|  3898K|  2192K  (2)|
    9. |   3 |   VIEW              |                         |   903 | 11739 | 24354   (1)|
    10. |*  4 |    TABLE ACCESS FULL| BL_CONTRACT_DIM         |   903 | 12642 | 24354   (1)|
    11.   ----------------------------------------------------------------------------------
    13.   Predicate Information (identified by operation id):
    14.   ---------------------------------------------------
    16.      2 - filter("A"."ETL_DATE">=TO_DATE(' 2016-12-19 00:00:00', 'syyyy-mm-dd 
    17.                 hh24:mi:ss') AND "A"."ETL_DATE"<=TO_DATE(' 2016-12-20 00:00:00', 'syyyy-mm-dd
    18.                 hh24:mi:ss'))
    19.      4 - filter("A"."OVERDUE_DD"="B"."OVERDUE_DD" OR "A"."DEALER_ID"="B"."DEALER_ID")

    从执行计划中看到，两表走的是嵌套循环。当两表用外连接进行关联，关联条件中有 OR 关联条件，那么这时只能走嵌套循环，而且驱动表固定为主表，此时不能走 HASH 连接，即使通过 HINT：USE_HASH 也无法修改执行计划。如果主表数据量很大，那么这时就会出现严重性能问题。我们可以将外连接的 OR 关联/过滤条件放到查询中，用 case when 进行过滤，从而让 SQL 可以走 HASH 连接。

    1. EXPLAIN PLAN FOR 
    2. SELECT A.CONTRACT_ID,
    3.        case
    4.          when A.DEALER_ID = B.DEALER_ID OR A.OVERDUE_DD = B.Overdue_Dd then
    5.           B.BORROWER_ID
    6.        end
    7.   FROM blfct.bl_rtl_con_overdue_fact A
    8.   LEFT JOIN BLpub.Bl_Contract_Dim B ON A.DEALER_ID = B.DEALER_ID
    9.  WHERE A.ETL_DATE BETWEEN DATE '2016-12-19' AND DATE '2016-12-20';

    执行计划如下。

    1. select * from table(dbms_xplan.display());
    3. Plan hash value: 3927476067
    5. -------------------------------------------------------------------------------------
    6. | Id |Operation          | Name                    |Rows | Bytes |TempSpc|Cost(%CPU)|
    7. -------------------------------------------------------------------------------------
    8. |  0 |SELECT STATEMENT   |                         |  57M|  1965M|       | 2218K (2)|
    9. |* 1 | HASH JOIN OUTER   |                         |  57M|  1965M|  6032K| 2218K (2)|
    10. |* 2 |  TABLE ACCESS FULL| BL_RTL_CON_OVERDUE_FACT | 181K|  3898K|       | 2192K (2)|
    11. |  3 |  TABLE ACCESS FULL| BL_CONTRACT_DIM         | 640K|  8763K|       |24349  (1)|
    12. -------------------------------------------------------------------------------------
    14.   Predicate Information (identified by operation id):
    15.   ---------------------------------------------------
    17.      1 - access("A"."DEALER_ID"="B"."DEALER_ID"(+))
    18.      2 - filter("A"."ETL_DATE">=TO_DATE(' 2016-12-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
    19.                 "A"."ETL_DATE"<=TO_DATE(' 2016-12-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

    利用 case when 改写外连接 OR 连接条件有个限制：从表只能是 1 的关系，不能是 n 的关系，从表要展示多少个列，就要写多少个 case when。我们利用 EMP 与 DEPT 进行讲解。EMP 与 DEPT 是 n∶1 关系，现有如下 SQL。

    1. select e.*, d.deptno deptno2, d.loc
    2.   from scott.emp e
    3.   left join scott.dept d on d.deptno = e.deptno
    4.                         and (d.deptno >= e.sal and e.sal < 1000 or
    5.                             e.ename like '%O%');

    执行计划如下。

    1. ----------------------------------------------------------
    2. Plan hash value: 2962868874
    4. -------------------------------------------------------------------------------------
    5. | Id | Operation                     | Name    | Rows  | Bytes | Cost(%CPU)|Time    |
    6. -------------------------------------------------------------------------------------
    7. |  0 | SELECT STATEMENT              |         |    14 |   826 |    17  (0)|00:00:01|
    8. |  1 |  NESTED LOOPS OUTER           |         |    14 |   826 |    17  (0)|00:00:01|
    9. |  2 |   TABLE ACCESS FULL           | EMP     |    14 |   532 |     3  (0)|00:00:01|
    10. |  3 |   VIEW                        |         |     1 |    21 |     1  (0)|00:00:01|
    11. |* 4 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1  (0)|00:00:01|
    12. |* 5 |     INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0  (0)|00:00:01|
    13. -------------------------------------------------------------------------------------
    15. Predicate Information (identified by operation id):
    16. ---------------------------------------------------
    18.    4 - filter("E"."ENAME" IS NOT NULL AND "E"."ENAME" IS NOT NULL AND
    19.               "E"."ENAME" LIKE '%O%' OR "D"."DEPTNO">="E"."SAL" AND "E"."SAL"<1000)
    20.    5 - access("D"."DEPTNO"="E"."DEPTNO")

    执行计划中两表关联走的是嵌套循环，驱动表是主表 EMP。现在我们添加 HINT：USE_HASH 尝试改变表连接方式。

    1.  select /*+ use_hash(e,d) */
    2.     e.*, d.deptno deptno2, d.loc
    3.     from scott.emp e
    4.     left join scott.dept d on d.deptno = e.deptno
    5.                           and (d.deptno >= e.sal and e.sal < 1000 or
    6.                               e.ename like '%O%');
    1. 14 rows selected.
    3. Execution Plan
    4. ----------------------------------------------------------
    5. Plan hash value: 2962868874
    6. -------------------------------------------------------------------------------------
    7. | Id | Operation                     | Name    | Rows  | Bytes | Cost(%CPU)|Time    |
    8. -------------------------------------------------------------------------------------
    9. |  0 | SELECT STATEMENT              |         |    14 |   826 |    17  (0)|00:00:01|
    10. |  1 |  NESTED LOOPS OUTER           |         |    14 |   826 |    17  (0)|00:00:01|
    11. |  2 |   TABLE ACCESS FULL           | EMP     |    14 |   532 |     3  (0)|00:00:01|
    12. |  3 |   VIEW                        |         |     1 |    21 |     1  (0)|00:00:01|
    13. |* 4 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1  (0)|00:00:01|
    14. |* 5 |     INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0  (0)|00:00:01|
    15. -------------------------------------------------------------------------------------
    17. Predicate Information (identified by operation id):
    18. ---------------------------------------------------
    20.    4 - filter("E"."ENAME" IS NOT NULL AND "E"."ENAME" IS NOT NULL AND
    21.               "E"."ENAME" LIKE '%O%' OR "D"."DEPTNO">="E"."SAL" AND "E"."SAL"<1000)
    22.    5 - access("D"."DEPTNO"="E"."DEPTNO")

    添加 HINT 无法更改执行计划。因为 SQL 语句中从表 DEPT 属于 1 的关系，从表 DEPT 要展示两个列，需要对应写上两个 case when。改写的 SQL 如下。

    1. select e.*,
    2.        case
    3.          when (d.deptno >= e.sal and e.sal < 1000 or e.ename like '%O%') then
    4.           d.deptno
    5.        end deptno2,
    6.        case
    7.          when (d.deptno >= e.sal and e.sal < 1000 or e.ename like '%O%') then
    8.           d.loc
    9.        end loc
    10.   from scott.emp e
    11.   left join scott.dept d on d.deptno = e.deptno;

    改写后的执行计划如下。

    1.  select e.*,
    2.          case
    3.            when (d.deptno >= e.sal and e.sal < 1000 or e.ename like '%O%') then
    4.            d.deptno
    5.          end deptno2,
    6.          case
    7.            when (d.deptno >= e.sal and e.sal < 1000 or e.ename like '%O%') then
    8.            d.loc
    9.          end loc
    10.     from scott.emp e
    11.     left join scott.dept d on d.deptno = e.deptno;
    1. 14 rows selected.
    3. Execution Plan
    4. ----------------------------------------------------------
    5. Plan hash value: 3387915970
    7. ---------------------------------------------------------------------------
    8. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    9. ---------------------------------------------------------------------------
    10. |   0 | SELECT STATEMENT   |      |    14 |   686 |     7  (15)| 00:00:01 |
    11. |*  1 |  HASH JOIN OUTER   |      |    14 |   686 |     7  (15)| 00:00:01 |
    12. |   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
    13. |   3 |   TABLE ACCESS FULL| DEPT |     4 |    44 |     3   (0)| 00:00:01 |
    14. ---------------------------------------------------------------------------
    16. Predicate Information (identified by operation id):
    17. ---------------------------------------------------
    19.    1 - access("D"."DEPTNO"(+)="E"."DEPTNO")

    用 case when 改写之后，两表自动走了 HASH 连接。

    如果主表属于 1 的关系，从表属于 n 的关系，我们就不能用 case when 进行等价改写，例子如下。

    1. select d.*, e.deptno deptno2, e.ename, e.sal
    2.   from dept d
    3.   left join emp e on d.deptno = e.deptno
    4.                  and (d.deptno >= e.sal and e.sal < 1000 or
    5.                      e.ename like '%O%');

    SQL 中 DEPT 是主表，EMP 是从表，DEPT 与 EMP 是 1∶n 的关系，此时不能将 SQL 改写为如下写法。

    1. select d.*,
    2.        case
    3.          when (d.deptno >= e.sal and e.sal < 1000 or e.ename like '%O%') then
    4.           e.deptno
    5.        end deptno2,
    6.        case
    7.          when (d.deptno >= e.sal and e.sal < 1000 or e.ename like '%O%') then
    8.           e.ename
    9.        end ename,
    10.        case
    11.          when (d.deptno >= e.sal and e.sal < 1000 or e.ename like '%O%') then
    12.           e.sal
    13.        end sal
    14.   from dept d
    15.   left join emp e on d.deptno = e.deptno;

    我们可以将 SQL 改写为如下写法。

    1. select b.*, a.deptno, a.ename, a.sal
    2.   from dept b
    3.   left join (select d.deptno, e.ename, e.sal
    4.                from dept d, emp e
    5.               where d.deptno = e.deptno
    6.                 and (d.deptno >= e.sal and e.sal < 1000 or
    7.                     e.ename like '%O%')) a on b.deptno = a.deptno;

    如果两表是 n∶n 关系，这时就无法对 SQL 进行改写了，在日常工作中一般也遇不到 n∶n 关系。