Filter conversion can be optimized at one time

Filter conversion can be optimized at one time

There is a problem with SQL Performance. When a filter is found in the execution plan, you should be careful when it comes to it. Like nestloop, my previous blog has studied it.Explore the filter principles in execution plans. Using exists can easily cause filter.

Before optimization:

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. WORKINGFROM GG_PD_PP_INFO GGPIWHERE 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 = '20140901' AND (GGPI. WORK_MASTER_UID = 'fa3502d1291a4a07a3b9e3e0f9a41904 'or exists (SELECT 1 FROM GG_PD_PP_WORK_MEMBER pmemwhere pmem. PROD_PLAN_ID = GGPI. idand pmem. WORK_MEMBER _ UID = 'fa3502d1291a4a07a3b9e3e0f9a41904 'and pmem. BUREAU_CODE = '000000'); Execution Plan ---------------------------------------------------------- Plan hash value: 0306 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop | -------------------------- California | 0 | select statement | 9670 | 2039K | 25694 (1) | 00:05:09 | * 1 | FILTER | 2 | partition list single | 72882 | 15M | 25694 (1) | 00:05:09 | KEY | 3 | partition range all | 72882 | 15M | 25694 (1) | 00:05:09 | 1 | 14 | * 4 | table access full | GG_PD_PP_INFO | 72882 | 15 M | 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 | descripredicate 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" = '000000') 4-filter ("PLAN_STATE" = 50 OR "PLAN_STATE" = 60 OR "PLAN_STATE" = 70) AND ("GGPI ". "PLAN_END_DATE"> SYSDATE @! AND "GGPI ". "PLAN_BEGIN_DATE" 5-filter ("PMEM ". "WORK_MEMBER_UID" = 'fa3502d1291a4a07a3b9e3e0f9a41904 'AND "PMEM ". "BUREAU_CODE" = '000000') 6-access ("PMEM ". "PROD_PLAN_ID" =: B1) Statistics limit 1 recursive calls0 db block gets103763 consistent gets0 physical reads0 redo limit bytes sent via SQL * Net to client524 bytes inclued via SQL * Net from client2 SQ L * Net roundtrips to/from client0 sorts (memory) 0 sorts (disk) 7 rows processed after optimization: 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. WORKINGFROM GG_PD_PP_INFO GGPIWHERE 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 = '20140901' and ggpi. WORK_MASTER_UID = 'fa3502d1291a4a07a3b9e3e0f9a41904 'union allSELECT 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. WORKINGFROM GG_PD_PP_INFO GGPIWHERE PLAN_STATE IN (50, 60, 70) AND (GGPI. PLA N_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 = '20140901' and ggpi. ID in (select pmem. PROD_PLAN_IDFROM GG_PD_PP_WORK_MEMBER pmemwhere pmem. WORK_MEMBER_UID = 'fa3502d1291a4a07a3b9e3e0f9a41904 'and pmem. BUREAU_CODE = '20140901'); Execution P Lan -------------------------------------------------------- Plan hash value: 1911592856 Bytes ---- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop | restart --------------------------------------------------------------------------------------------------------- ---------------------------- | 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 | * 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 | accuracy ---- 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" "GGPI ". "BUREAU_CODE" = '000000') 3-access ("GGPI ". "WORK_MASTER_UID" = 'fa3502d1291a4a07a3b9e3e0f9a41904 ') 7-filter ("PMEM ". "BUREAU_CODE" = '000000') 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" "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" = '000000 ') statistics limit 1 recursive calls0 db block limit consistent gets550 physical reads0 redo limit bytes sent via SQL * Net to client524 bytes encoded ed via SQL * Net from client2 SQL * Net roundtrips to/from client1 sorts (memory) 0 sorts (disk) 7 rows processed