一次性能优化将filter转换

  有一条SQL性能有问题，在运行计划中发现filter。遇到它要小心了，类似于nestloop。我曾经的blog对它有研究探索运行计划中filter的原理.用exists极易引起filter.

优化前：

SELECT GGPI.ID,

       GGPI.PLAN_STATE,
       GGPI.PLAN_TYPE,
       GGPI.PLAN_CODE,
       GGPI.PLAN_SOURCE_TYPE,
       GGPI.PLAN_BEGIN_DATE,
       GGPI.PLAN_END_DATE,
       GGPI.REAL_BEGIN_DATE,
       GGPI.REAL_END_DATE,
       GGPI.WORK_MASTER_UNAME,
       GGPI.WORK_MASTER_UID,
       GGPI.WORK_TEAM_ONAME,
       GGPI.WORK_SITE_NAMES,
       GGPI.WORK_CONTENT,
       GGPI.WORK_TYPE,
       GGPI.WORK_MEMBER_UNAMES,
       GGPI.DIGGATCH_WORK_UNAME,
       GGPI.WORKING
  FROM GG_PD_PP_INFO GGPI
 WHERE PLAN_STATE IN (50, 60, 70)
   AND ((GGPI.PLAN_BEGIN_DATE >= sysdate and GGPI.PLAN_BEGIN_DATE <= sysdate) or
       (GGPI.PLAN_END_DATE >= sysdate and GGPI.PLAN_END_DATE <= sysdate) or
       (GGPI.PLAN_BEGIN_DATE < sysdate and GGPI.PLAN_END_DATE > sysdate))
   AND GGPI.BUREAU_CODE = '0306'
   AND (GGPI.WORK_MASTER_UID ='FA3502D1291A4A07A3B9E3E0F9A41904' OR EXISTS
        (SELECT 1
           FROM GG_PD_PP_WORK_MEMBER PMEM
          WHERE PMEM.PROD_PLAN_ID = GGPI.ID
            AND PMEM.WORK_MEMBER_UID =  'FA3502D1291A4A07A3B9E3E0F9A41904'
            AND PMEM.BUREAU_CODE = '0306'));
Execution Plan
----------------------------------------------------------
Plan hash value: 980323934
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                         |  9670 |  2039K| 25694   (1)| 00:05:09 |       |       |
|*  1 |  FILTER                             |                         |       |       |            |          |       |       |
|   2 |   PARTITION LIST SINGLE             |                         | 72882 |    15M| 25694   (1)| 00:05:09 |   KEY |   KEY |
|   3 |    PARTITION RANGE ALL              |                         | 72882 |    15M| 25694   (1)| 00:05:09 |     1 |    14 |
|*  4 |     TABLE ACCESS FULL               | GG_PD_PP_INFO           | 72882 |    15M| 25694   (1)| 00:05:09 |       |       |
|*  5 |   TABLE ACCESS BY GLOBAL INDEX ROWID| GG_PD_PP_WORK_MEMBER    |     1 |    49 |     5   (0)| 00:00:01 |     4 |     4 |
|*  6 |    INDEX RANGE SCAN                 | IDX_PP_WORK_MEMBER_PPID |     6 |       |     3   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("GGPI"."WORK_MASTER_UID" IS NULL OR  EXISTS (SELECT 0 FROM "GG_PD_PP_WORK_MEMBER"
              "PMEM" WHERE "PMEM"."PROD_PLAN_ID"=:B1 AND "PMEM"."WORK_MEMBER_UID"='FA3502D1291A4A07A3B9E3E0F9A41904' AND
              "PMEM"."BUREAU_CODE"='0306'))
   4 - filter(("PLAN_STATE"=50 OR "PLAN_STATE"=60 OR "PLAN_STATE"=70) AND ("GGPI"."PLAN_END_DATE">SYSDATE@! AND
              "GGPI"."PLAN_BEGIN_DATE"<SYSDATE@! OR "GGPI"."PLAN_BEGIN_DATE"=SYSDATE@! OR "GGPI"."PLAN_END_DATE"=SYSDATE@!))
   5 - filter("PMEM"."WORK_MEMBER_UID"='FA3502D1291A4A07A3B9E3E0F9A41904' AND "PMEM"."BUREAU_CODE"='0306')
   6 - access("PMEM"."PROD_PLAN_ID"=:B1)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     103763  consistent gets
          0  physical reads
          0  redo size
       3178  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          7  rows processed
          

优化后：
SELECT GGPI.ID,
       GGPI.PLAN_STATE,
       GGPI.PLAN_TYPE,
       GGPI.PLAN_CODE,
       GGPI.PLAN_SOURCE_TYPE,
       GGPI.PLAN_BEGIN_DATE,
       GGPI.PLAN_END_DATE,
       GGPI.REAL_BEGIN_DATE,
       GGPI.REAL_END_DATE,
       GGPI.WORK_MASTER_UNAME,
       GGPI.WORK_MASTER_UID,
       GGPI.WORK_TEAM_ONAME,
       GGPI.WORK_SITE_NAMES,
       GGPI.WORK_CONTENT,
       GGPI.WORK_TYPE,
       GGPI.WORK_MEMBER_UNAMES,
       GGPI.DIGGATCH_WORK_UNAME,
       GGPI.WORKING
  FROM GG_PD_PP_INFO GGPI
 WHERE PLAN_STATE IN (50, 60, 70)
   AND ((GGPI.PLAN_BEGIN_DATE >= sysdate and GGPI.PLAN_BEGIN_DATE <= sysdate) or
       (GGPI.PLAN_END_DATE >= sysdate and GGPI.PLAN_END_DATE <= sysdate) or
       (GGPI.PLAN_BEGIN_DATE < sysdate and GGPI.PLAN_END_DATE > sysdate))
   AND GGPI.BUREAU_CODE = '0306'
   AND GGPI.WORK_MASTER_UID ='FA3502D1291A4A07A3B9E3E0F9A41904' 
union all
SELECT GGPI.ID,
       GGPI.PLAN_STATE,
       GGPI.PLAN_TYPE,
       GGPI.PLAN_CODE,
       GGPI.PLAN_SOURCE_TYPE,
       GGPI.PLAN_BEGIN_DATE,
       GGPI.PLAN_END_DATE,
       GGPI.REAL_BEGIN_DATE,
       GGPI.REAL_END_DATE,
       GGPI.WORK_MASTER_UNAME,
       GGPI.WORK_MASTER_UID,
       GGPI.WORK_TEAM_ONAME,
       GGPI.WORK_SITE_NAMES,
       GGPI.WORK_CONTENT,
       GGPI.WORK_TYPE,
       GGPI.WORK_MEMBER_UNAMES,
       GGPI.DIGGATCH_WORK_UNAME,
       GGPI.WORKING
  FROM GG_PD_PP_INFO GGPI
 WHERE PLAN_STATE IN (50, 60, 70)
   AND ((GGPI.PLAN_BEGIN_DATE >= sysdate and GGPI.PLAN_BEGIN_DATE <= sysdate) or
       (GGPI.PLAN_END_DATE >= sysdate and GGPI.PLAN_END_DATE <= sysdate) or
       (GGPI.PLAN_BEGIN_DATE < sysdate and GGPI.PLAN_END_DATE > sysdate))
   AND GGPI.BUREAU_CODE = '0306'
   AND GGPI.ID in
        (SELECT PMEM.PROD_PLAN_ID
           FROM GG_PD_PP_WORK_MEMBER PMEM
          WHERE PMEM.WORK_MEMBER_UID =  'FA3502D1291A4A07A3B9E3E0F9A41904'
            AND PMEM.BUREAU_CODE = '0306');  
Execution Plan
----------------------------------------------------------
Plan hash value: 1911592856
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                          |  1227 |   316K|  3740   (1)| 00:00:45 |       |       |
|   1 |  UNION-ALL                             |                          |       |       |            |          |       |       |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID   | GG_PD_PP_INFO            |    22 |  4752 |   160   (0)| 00:00:02 | ROWID | ROWID |
|*  3 |    INDEX RANGE SCAN                    | IND_GGPI_WORK_MASTER_UID |   222 |       |     5   (0)| 00:00:01 |       |       |
|   4 |   NESTED LOOPS                         |                          |  1205 |   311K|  3580   (1)| 00:00:43 |       |       |
|   5 |    NESTED LOOPS                        |                          |  1205 |   311K|  3580   (1)| 00:00:43 |       |       |
|   6 |     SORT UNIQUE                        |                          |  1205 | 59045 |  2373   (1)| 00:00:29 |       |       |
|*  7 |      TABLE ACCESS BY GLOBAL INDEX ROWID| GG_PD_PP_WORK_MEMBER     |  1205 | 59045 |  2373   (1)| 00:00:29 |     4 |     4 |
|*  8 |       INDEX RANGE SCAN                 | IND_GGPW_WORK_MEMBER_UID |  4240 |       |    31   (0)| 00:00:01 |       |       |
|*  9 |     INDEX UNIQUE SCAN                  | PK_GG_PD_PP_INFO         |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 10 |    TABLE ACCESS BY GLOBAL INDEX ROWID  | GG_PD_PP_INFO            |     1 |   216 |     2   (0)| 00:00:01 | ROWID | ROWID |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("PLAN_STATE"=50 OR "PLAN_STATE"=60 OR "PLAN_STATE"=70) AND ("GGPI"."PLAN_END_DATE">SYSDATE@! AND
              "GGPI"."PLAN_BEGIN_DATE"<SYSDATE@! OR "GGPI"."PLAN_BEGIN_DATE"=SYSDATE@! OR "GGPI"."PLAN_END_DATE"=SYSDATE@!) AND
              "GGPI"."BUREAU_CODE"='0306')
   3 - access("GGPI"."WORK_MASTER_UID"='FA3502D1291A4A07A3B9E3E0F9A41904')
   7 - filter("PMEM"."BUREAU_CODE"='0306')
   8 - access("PMEM"."WORK_MEMBER_UID"='FA3502D1291A4A07A3B9E3E0F9A41904')
   9 - access("GGPI"."ID"="PMEM"."PROD_PLAN_ID")
  10 - filter(("GGPI"."PLAN_END_DATE">SYSDATE@! AND "GGPI"."PLAN_BEGIN_DATE"<SYSDATE@! OR
              "GGPI"."PLAN_BEGIN_DATE"=SYSDATE@! OR "GGPI"."PLAN_END_DATE"=SYSDATE@!) AND ("PLAN_STATE"=50 OR "PLAN_STATE"=60 OR
              "PLAN_STATE"=70) AND "GGPI"."BUREAU_CODE"='0306')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4321  consistent gets
        550  physical reads
          0  redo size
       3228  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          7  rows processed