explain plan for select ‘33101‘ org_no, nvl(count(*), 0) outNum from CPSS.k_id_staff_tqjl_v a where a.pda_status_code = ‘002‘ and a.gds_no in (SELECT b.dim_value FROM CPSS.k_ic_dim_value b WHERE b.level_id = ‘5‘ AND b.dim_code = ‘org‘ START WITH b.dim_value = ‘33101‘ CONNECT BY PRIOR b.dim_value = b.p_dim_id) and not exists ( select 1 from CPSS.K_PDA_HEARTBEAT k where date_str = ‘20200714‘ and GMT_CREATE >= sysdate - 15 / 24 / 60 and k.user_no = a.auth_user_no and k.org_no = a.gds_no ); Plan hash value: 1785689430 --------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 94 | 39 (3)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 94 | | | | | |* 2 | FILTER | | | | | | | | |* 3 | HASH JOIN | | 2 | 188 | 37 (3)| 00:00:01 | | | | 4 | NESTED LOOPS | | 53 | 3445 | 36 (3)| 00:00:01 | | | | 5 | NESTED LOOPS | | 294 | 3445 | 36 (3)| 00:00:01 | | | | 6 | VIEW | VW_NSO_1 | 6 | 204 | 14 (8)| 00:00:01 | | | | 7 | HASH UNIQUE | | 6 | 618 | 14 (8)| 00:00:01 | | | |* 8 | FILTER | | | | | | | | |* 9 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | | | | | 10 | TABLE ACCESS FULL | K_IC_DIM_VALUE | 2579 | 98K| 13 (0)| 00:00:01 | | | |* 11 | INDEX RANGE SCAN | IDX_K_ID_STAFF_N1 | 49 | | 1 (0)| 00:00:01 | | | |* 12 | TABLE ACCESS BY INDEX ROWID | K_ID_STAFF | 9 | 279 | 4 (0)| 00:00:01 | | | | 13 | TABLE ACCESS BY INDEX ROWID | K_IC_DIM_VALUE | 96 | 2784 | 1 (0)| 00:00:01 | | | |* 14 | INDEX RANGE SCAN | IDX_K_IC_DIM_VALUE_N1 | 96 | | 1 (0)| 00:00:01 | | | | 15 | PARTITION RANGE SINGLE | | 1 | 37 | 1 (0)| 00:00:01 | 8 | 8 | |* 16 | TABLE ACCESS BY LOCAL INDEX ROWID | K_PDA_HEARTBEAT | 1 | 37 | 1 (0)| 00:00:01 | 8 | 8 | |* 17 | INDEX RANGE SCAN | IDX_GMT_CREATE_USER_ORG2 | 1 | | 1 (0)| 00:00:01 | 8 | 8 | |* 18 | COUNT STOPKEY | | | | | | | | | 19 | TABLE ACCESS BY INDEX ROWID | P_PDA_DEVICE | 2 | 20 | 1 (0)| 00:00:01 | | | |* 20 | INDEX RANGE SCAN | IDX_P_PDA_DEVICE_N1 | 1 | | 1 (0)| 00:00:01 | | | --------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter( NOT EXISTS (SELECT 0 FROM "CPSS"."K_PDA_HEARTBEAT" "K" WHERE "GMT_CREATE" IS NOT NULL AND "GMT_CREATE">=SYSDATE@!-.0104166666666666666666666666666666666667 AND "DATE_STR"=‘20200714‘ AND "K"."USER_NO"=:B1 AND "K"."ORG_NO"=:B2) AND (SELECT "D"."STATUS_CODE" FROM CPSS."P_PDA_DEVICE" "D" WHERE ROWNUM=1 AND "D"."FETCHER_NO"=:B3)=‘002‘) 3 - access("S"."ORG_NO"="D"."DIM_VALUE") 8 - filter("B"."LEVEL_ID"=5 AND "B"."DIM_CODE"=‘org‘) 9 - access("B"."P_DIM_ID"=PRIOR "B"."DIM_VALUE") filter("B"."DIM_VALUE"=‘33101‘) 11 - access("S"."ORG_NO"="DIM_VALUE") 12 - filter("S"."POST"=‘17‘ AND "S"."WORK_MODE"=‘01‘ AND "S"."STAFF_STATE"=‘01‘ AND "S"."DATA_OPER_TYPE"<>‘D‘) 14 - access("D"."DIM_CODE"=‘org‘ AND "D"."LEVEL_ID"=5) 16 - filter("DATE_STR"=‘20200714‘) 17 - access("GMT_CREATE">=SYSDATE@!-.0104166666666666666666666666666666666667 AND "K"."USER_NO"=:B1 AND "K"."ORG_NO"=:B2 AND "GMT_CREATE" IS NOT NULL) filter("K"."USER_NO"=:B1 AND "K"."ORG_NO"=:B2) 18 - filter(ROWNUM=1) 20 - access("D"."FETCHER_NO"=:B1)
explain plan for select ‘33101‘ org_no, nvl(count(*), 0) outNum from CPSS.k_id_staff_tqjl_v a where a.pda_status_code = ‘002‘ and a.gds_no in (SELECT b.dim_value FROM CPSS.k_ic_dim_value b WHERE b.level_id = ‘5‘ AND b.dim_code = ‘org‘ START WITH b.dim_value = ‘33101‘ CONNECT BY PRIOR b.dim_value = b.p_dim_id) and (auth_user_no,gds_no) not in (select/*+qb_name(zz)*/ user_no,org_no from CPSS.K_PDA_HEARTBEAT k where date_str = ‘20200714‘ and GMT_CREATE >= sysdate - 15 / 24 / 60); Plan hash value: 3515795332 ----------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 131 | 44 (5)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 131 | | | | | |* 2 | FILTER | | | | | | | | |* 3 | HASH JOIN | | 9 | 1179 | 39 (6)| 00:00:01 | | | | 4 | MERGE JOIN ANTI NA | | 53 | 5406 | 38 (6)| 00:00:01 | | | | 5 | SORT JOIN | | 53 | 3445 | 36 (3)| 00:00:01 | | | | 6 | NESTED LOOPS | | 53 | 3445 | 36 (3)| 00:00:01 | | | | 7 | NESTED LOOPS | | 294 | 3445 | 36 (3)| 00:00:01 | | | | 8 | VIEW | VW_NSO_1 | 6 | 204 | 14 (8)| 00:00:01 | | | | 9 | HASH UNIQUE | | 6 | 618 | 14 (8)| 00:00:01 | | | |* 10 | FILTER | | | | | | | | |* 11 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | | | | | 12 | TABLE ACCESS FULL | K_IC_DIM_VALUE | 2579 | 98K| 13 (0)| 00:00:01 | | | |* 13 | INDEX RANGE SCAN | IDX_K_ID_STAFF_N1 | 49 | | 1 (0)| 00:00:01 | | | |* 14 | TABLE ACCESS BY INDEX ROWID | K_ID_STAFF | 9 | 279 | 4 (0)| 00:00:01 | | | |* 15 | SORT UNIQUE | | 2 | 74 | 3 (34)| 00:00:01 | | | | 16 | PARTITION RANGE SINGLE | | 2 | 74 | 2 (0)| 00:00:01 | 8 | 8 | |* 17 | TABLE ACCESS BY LOCAL INDEX ROWID | K_PDA_HEARTBEAT | 2 | 74 | 2 (0)| 00:00:01 | 8 | 8 | |* 18 | INDEX RANGE SCAN | IDX_GMT_CREATE_USER_ORG2 | 7 | | 1 (0)| 00:00:01 | 8 | 8 | | 19 | TABLE ACCESS BY INDEX ROWID | K_IC_DIM_VALUE | 96 | 2784 | 1 (0)| 00:00:01 | | | |* 20 | INDEX RANGE SCAN | IDX_K_IC_DIM_VALUE_N1 | 96 | | 1 (0)| 00:00:01 | | | |* 21 | COUNT STOPKEY | | | | | | | | | 22 | TABLE ACCESS BY INDEX ROWID | P_PDA_DEVICE | 2 | 20 | 1 (0)| 00:00:01 | | | |* 23 | INDEX RANGE SCAN | IDX_P_PDA_DEVICE_N1 | 1 | | 1 (0)| 00:00:01 | | | ----------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter( (SELECT "D"."STATUS_CODE" FROM CPSS."P_PDA_DEVICE" "D" WHERE ROWNUM=1 AND "D"."FETCHER_NO"=:B1)=‘002‘) 3 - access("S"."ORG_NO"="D"."DIM_VALUE") 10 - filter("B"."LEVEL_ID"=5 AND "B"."DIM_CODE"=‘org‘) 11 - access("B"."P_DIM_ID"=PRIOR "B"."DIM_VALUE") filter("B"."DIM_VALUE"=‘33101‘) 13 - access("S"."ORG_NO"="DIM_VALUE") 14 - filter("S"."POST"=‘17‘ AND "S"."WORK_MODE"=‘01‘ AND "S"."STAFF_STATE"=‘01‘ AND "S"."DATA_OPER_TYPE"<>‘D‘) 15 - access(INTERNAL_FUNCTION("S"."ORG_NO")=INTERNAL_FUNCTION("ORG_NO") AND INTERNAL_FUNCTION("S"."AUTH_USER_NO")=INTERNAL_FUNCTION("USER_NO")) filter(INTERNAL_FUNCTION("S"."ORG_NO")=INTERNAL_FUNCTION("ORG_NO") AND INTERNAL_FUNCTION("S"."AUTH_USER_NO")=INTERNAL_FUNCTION("USER_NO")) 17 - filter("DATE_STR"=‘20200714‘) 18 - access("GMT_CREATE">=SYSDATE@!-.0104166666666666666666666666666666666667 AND "GMT_CREATE" IS NOT NULL) 20 - access("D"."DIM_CODE"=‘org‘ AND "D"."LEVEL_ID"=5) 21 - filter(ROWNUM=1) 23 - access("D"."FETCHER_NO"=:B1)
实际生产环境中常常能见到执行计划中出现filter
filter有哪些坏处:
1.出现后驱动表固定,优化器不能选择合适的驱动表
2.filter的表关联方式类似与nesloop,对于驱动表数据集比较大的情况下,不适合用改关联方式
上述sql
select ‘33101‘ org_no, nvl(count(*), 0) outNum from CPSS.k_id_staff_tqjl_v a where a.pda_status_code = ‘002‘ and a.gds_no in (SELECT b.dim_value FROM CPSS.k_ic_dim_value b WHERE b.level_id = ‘5‘ AND b.dim_code = ‘org‘ START WITH b.dim_value = ‘33101‘ CONNECT BY PRIOR b.dim_value = b.p_dim_id)
改sql执行效率ok,返回结果集有1万多(毫秒级别)。
加入not exists后,查询效率大幅度降低,需要三秒多。
执行计划我们看到走的是filter表关联方式
用not in改写后走的是排序合并连接,查询耗时又恢复导毫秒级别。