Oracle SQL FILTER 优化技巧与案例分析

下面这个SQL要跑5个多小时

SELECT 
               B.AREA_ID,
               A.PARTY_ID,
               B.AREA_NAME,
               C.NAME           CHANNEL_NAME,
               B.NAME           PARTY_NAME,
               B.ACCESS_NUMBER,
               B.PROD_SPEC,
               B.START_DT,
               A.BO_ACTION_NAME,
               A.SO_STAFF_ID,
               A.ATOM_ACTION_ID,
               A.PROD_ID 
        FROM   DW_CHANNEL      C,
               DW_CRM_DAY_USER B,
               DW_BO_ORDER     A
        WHERE  A.PROD_ID = B.PROD_ID AND
               A.CHANNEL_ID = C.CHANNEL_ID AND
               A.SO_STAFF_ID LIKE '36%' AND
               A.BO_ACTION_NAME IN ('新装','移机','资费变更') AND
               B.PROD_SPEC IN ('普通电话', 'ADSL','LAN', '手机',
                               'E8 - 2S','E6移动版', 'E9版1M(老版)',
                               '普通E9','普通新版E8',
                               '全省_紧密融合型E9套餐产品规格',
                               '(新) 全省_紧密融合型E9套餐产品规格',
                               '新春欢乐送之E8套餐',
                               '新春欢乐送之E6套餐') AND
              NOT  EXISTS (SELECT  * 

                FROM   DW_BO_ORDER D
                WHERE  D.STAFF_ID LIKE '36%' AND
                       A.PARTY_ID = D.PARTY_ID AND
                       A.BO_ID != D.BO_ID AND
                       A.PROD_ID != D.PROD_ID AND
                       A.BO_ACTION_NAME IN
                       ('新装', '移机','资费变更') AND
                       A.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT);

执行计划和相关信息如下：

Plan hash value: 2142862569
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |   905 |   121K|  4152K  (2)| 13:50:32 |       |       |
|*  1 |  FILTER                |                 |       |       |            |          |       |       |
|*  2 |   HASH JOIN            |                 |   905 |   121K| 12616   (2)| 00:02:32 |       |       |
|*  3 |    HASH JOIN           |                 |   905 | 99550 | 12448   (2)| 00:02:30 |       |       |
|   4 |     PARTITION RANGE ALL|                 |  1979 |   108K|  9168   (2)| 00:01:51 |     1 |     5 |
|*  5 |      TABLE ACCESS FULL | DW_BO_ORDER     |  1979 |   108K|  9168   (2)| 00:01:51 |     1 |     5 |
|*  6 |     TABLE ACCESS FULL  | DW_CRM_DAY_USER |   309K|    15M|  3277   (2)| 00:00:40 |       |       |
|   7 |    TABLE ACCESS FULL   | DW_CHANNEL      | 48425 |  1276K|   168   (1)| 00:00:03 |       |       |
|*  8 |   FILTER               |                 |       |       |            |          |       |       |
|   9 |    PARTITION RANGE ALL |                 |     1 |    29 |  9147   (2)| 00:01:50 |     1 |     5 |
|* 10 |     TABLE ACCESS FULL  | DW_BO_ORDER     |     1 |    29 |  9147   (2)| 00:01:50 |     1 |     5 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):