10M问题：sql执行时正常应该走索引扫描T_BUSI_RUN表根据时间筛选少量数据，从执行计划来看时间筛选条件未生效，走的全表扫描，改写sql后执行计划正常
除了改写sql，是否可以从数据库层面解决
sql及执行计划：
select count(YYID) YYCS,
sum(YYLC) YYLC,
sum(KSLC) KSLC,
sum(DHSJ) DHSJ,
sum(YYJE) YYJE
from (SELECT YYLC, YYJE, KSLC, DHSJ, YYID, SJDM, SCSJ
FROM T_BUSI_RUN
LEFT JOIN T_BASE_DEV ON T_BUSI_RUN.DEV_ID = T_BASE_DEV.DEV_ID
LEFT JOIN T_BASE_VEHICLE ON T_BUSI_RUN.Cphm = T_BASE_VEHICLE.Vehicleid
right JOIN (SELECT ID_OWNER FROM T_BASE_USER_COM WHERE USERID = 1050) T1 ON T1.ID_OWNER = T_BASE_VEHICLE.ID_OWNER
and SCSJ >= to_date(‘2024-08-01 00:00:00’,‘YYYY-MM-DD HH24:MI:SS’)
AND SCSJ <= to_date(‘2024-08-01 22:02:21’,‘YYYY-MM-DD HH24:MI:SS’)) T
order by SCSJ desc;
PLAN_TABLE_OUTPUT
Plan hash value: 346898854
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
| 0 | SELECT STATEMENT | | 1 | 65 | 368M (1)|999:59:59 | | |
| 1 | SORT AGGREGATE | | 1 | 65 | | | | |
| 2 | VIEW | | 122M| 7588M| 368M (1)|999:59:59 | | |
| 3 | VIEW | | 122M| 7588M| 368M (1)|999:59:59 | | |
| 4 | NESTED LOOPS | | 122M| 9G| 368M (1)|999:59:59 | | |
| 5 | VIEW | | 2 | 26 | 2 (0)| 00:00:01 | | |
|* 6 | INDEX RANGE SCAN | PK_T_BASE_USER_COM | 2 | 16 | 2 (0)| 00:00:01 | | |
|* 7 | VIEW | | 61M| 4319M| 184M (1)|614:06:25 | | |
|* 8 | VIEW | | 61M| 5078M| 184M (1)|614:06:25 | | |
| 9 | NESTED LOOPS OUTER | | 61M| 5778M| 184M (1)|614:06:25 | | |
| 10 | VIEW | | 61M| 5020M| 61M (1)|205:57:38 | | |
| 11 | NESTED LOOPS OUTER | | 61M| 2743M| 61M (1)|205:57:38 | | |
| 12 | PARTITION RANGE ALL | | 61M| 2743M| 565K (1)| 01:53:06 | 1 |1048575|
| 13 | TABLE ACCESS FULL | T_BUSI_RUN | 61M| 2743M| 565K (1)| 01:53:06 | 1 |1048575|
| 14 | VIEW | | 1 | | 1 (0)| 00:00:01 | | |
|* 15 | INDEX UNIQUE SCAN | PK_T_BASE_DEV | 1 | 7 | 1 (0)| 00:00:01 | | |
| 16 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | | |
| 17 | TABLE ACCESS BY INDEX ROWID| T_BASE_VEHICLE | 1 | 14 | 2 (0)| 00:00:01 | | |
|* 18 | INDEX RANGE SCAN | T_BASE_VEHICLE_VEHICLEID_IDX | 1 | | 1 (0)| 00:00:01 | | |
Predicate Information (identified by operation id):
6 - access(“USERID”=1050)
7 - filter(“from_006”.“SCSJ”>=TO_DATE(’ 2024-08-01 22:00:00’, ‘syyyy-mm-dd hh24:mi:ss’) AND
“from_006”.“SCSJ”<=TO_DATE(’ 2024-08-01 22:21:22’, ‘syyyy-mm-dd hh24:mi:ss’))
8 - filter(“T1”.“ID_OWNER”=“T_BASE_VEHICLE”.“ID_OWNER”)
15 - access(“T_BUSI_RUN”.“DEV_ID”=“T_BASE_DEV”.“DEV_ID”)
18 - access(“T_BUSI_RUN”.“CPHM”=“T_BASE_VEHICLE”.“VEHICLEID”)
35 rows selected.
sql改写：where 改为and
where SCSJ >= to_date(‘2024-08-01 00:00:00’,‘YYYY-MM-DD HH24:MI:SS’)
AND SCSJ <= to_date(‘2024-08-01 22:02:21’,‘YYYY-MM-DD HH24:MI:SS’)) —>>>
and SCSJ >= to_date(‘2024-08-01 00:00:00’,‘YYYY-MM-DD HH24:MI:SS’)
AND SCSJ <= to_date(‘2024-08-01 22:02:21’,‘YYYY-MM-DD HH24:MI:SS’))
新执行计划：
PLAN_TABLE_OUTPUT
Plan hash value: 1552807778
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
| 0 | SELECT STATEMENT | | 1 | 68 | 31817 (1)| 00:06:22 | | |
| 1 | SORT AGGREGATE | | 1 | 68 | | | | |
| 2 | NESTED LOOPS | | 1 | 68 | 31817 (1)| 00:06:22 | | |
| 3 | NESTED LOOPS | | 1 | 60 | 31817 (1)| 00:06:22 | | |
|* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID | T_BUSI_RUN | 1 | 46 | 31815 (1)| 00:06:22 | 1 | 1 |
|* 5 | INDEX RANGE SCAN | T_BUSI_RUN_SCSJ_INDEX | 93 | | 230 (0)| 00:00:03 | | |
| 6 | TABLE ACCESS BY INDEX ROWID | T_BASE_VEHICLE | 1 | 14 | 2 (0)| 00:00:01 | | |
|* 7 | INDEX RANGE SCAN | T_BASE_VEHICLE_VEHICLEID_IDX| 1 | | 1 (0)| 00:00:01 | | |
|* 8 | INDEX UNIQUE SCAN | PK_T_BASE_USER_COM | 1 | 8 | 0 (0)| 00:00:01 | | |
Predicate Information (identified by operation id):
4 - filter(“T_BUSI_RUN”.“DEV_ID”=200040274)
5 - access(“T_BUSI_RUN”.“SCSJ”>=TO_DATE(’ 2017-10-26 09:00:00’, ‘syyyy-mm-dd hh24:mi:ss’) AND
“T_BUSI_RUN”.“SCSJ”<=TO_DATE(’ 2017-10-26 20:49:22’, ‘syyyy-mm-dd hh24:mi:ss’))
7 - access(“T_BUSI_RUN”.“CPHM”=“T_BASE_VEHICLE”.“VEHICLEID”)
8 - access(“USERID”=684 AND “ID_OWNER”=“T_BASE_VEHICLE”.“ID_OWNER”)
24 rows selected.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 346898854
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 368M (1)|999:59:59 | | |
| 1 | SORT AGGREGATE | | 1 | 65 | | | | |
| 2 | VIEW | | 122M| 7588M| 368M (1)|999:59:59 | | |
| 3 | VIEW | | 122M| 7588M| 368M (1)|999:59:59 | | |
| 4 | NESTED LOOPS | | 122M| 9G| 368M (1)|999:59:59 | | |
| 5 | VIEW | | 2 | 26 | 2 (0)| 00:00:01 | | |
|* 6 | INDEX RANGE SCAN | PK_T_BASE_USER_COM | 2 | 16 | 2 (0)| 00:00:01 | | |
|* 7 | VIEW | | 61M| 4319M| 184M (1)|614:06:25 | | |
|* 8 | VIEW | | 61M| 5078M| 184M (1)|614:06:25 | | |
| 9 | NESTED LOOPS OUTER | | 61M| 5778M| 184M (1)|614:06:25 | | |
| 10 | VIEW | | 61M| 5020M| 61M (1)|205:57:38 | | |
| 11 | NESTED LOOPS OUTER | | 61M| 2743M| 61M (1)|205:57:38 | | |
| 12 | PARTITION RANGE ALL | | 61M| 2743M| 565K (1)| 01:53:06 | 1 |1048575|
| 13 | TABLE ACCESS FULL | T_BUSI_RUN | 61M| 2743M| 565K (1)| 01:53:06 | 1 |1048575|
| 14 | VIEW | | 1 | | 1 (0)| 00:00:01 | | |
|* 15 | INDEX UNIQUE SCAN | PK_T_BASE_DEV | 1 | 7 | 1 (0)| 00:00:01 | | |
| 16 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | | |
| 17 | TABLE ACCESS BY INDEX ROWID| T_BASE_VEHICLE | 1 | 14 | 2 (0)| 00:00:01 | | |
|* 18 | INDEX RANGE SCAN | T_BASE_VEHICLE_VEHICLEID_IDX | 1 | | 1 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("USERID"=1050)
7 - filter("from$_subquery$_006"."SCSJ">=TO_DATE(' 2024-08-01 22:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ---条件未生效
"from$_subquery$_006"."SCSJ"<=TO_DATE(' 2024-08-01 22:21:22', 'syyyy-mm-dd hh24:mi:ss'))
8 - filter("T1"."ID_OWNER"="T_BASE_VEHICLE"."ID_OWNER")
15 - access("T_BUSI_RUN"."DEV_ID"="T_BASE_DEV"."DEV_ID")
18 - access("T_BUSI_RUN"."CPHM"="T_BASE_VEHICLE"."VEHICLEID")
35 rows selected.
评论
有用 0墨值悬赏