sql优化案例
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改写后走的是排序合并连接，查询耗时又恢复导毫秒级别。
