3,491
社区成员
发帖
与我相关
我的任务
分享SQL 语句优化
SELECT B.LGTD,B.LTTD,A.STCD,B.STNM,B.ADDVCD,'ZZ' STTP,B.FRGRD,A.TM,A.Z VALUE FROM A, B
WHERE (NOT EXISTS(SELECT * FROM C WHERE STCD=A.STCD AND TM>A.TM AND TM>to_date('2011-08-11','yyyy-mm-dd') AND TM<=to_date('2011-08-14','yyyy-mm-dd')))
AND A.TM>to_date('2011-08-11','yyyy-mm-dd') AND TM<=to_date('2011-08-14','yyyy-mm-dd') AND A.STCD=B.STCD AND B.STTP IN('ZZ','ZQ','PP','DD') AND B.LGTD IS NOT NULL
AND B.LTTD IS NOT NULL AND ADDVCD IS NOT NULL;现在有上面的一条SQL语句想进行优化，因为对于SQL不是太熟，只是听说子查询速度比较慢，所以想通过 join 的方式来处理，后来请同事写了一个，但效率并没有提高多少，所以想请大家帮忙看看，感谢～～
SELECT D.LGTD,D.LTTD,C.STCD,D.STNM,D.ADDVCD,'ZZ' STTP,D.FRGRD,C.TM,C.Z VAL
FROM
(select stcd,max(tm) maxtm from A
where tm > to_date('2011-08-11','yyyy-mm-dd') AND TM<=to_date('2011-08-14','yyyy-mm-dd')
group by stcd
) B
inner join C ON B.STCD = C.STCD
left join D ON C.STCD = D.STCD
where B.maxtm = C.TM and D.STTP IN('ZZ','ZQ','PP','DD') AND D.LGTD IS NOT NULL
AND D.LTTD IS NOT NULL AND D.ADDVCD IS NOT NULL;这个是同事写的SQL～～
...全文
请发表友善的回复…
发表回复
bw555 2014-08-12
- 打赏
- 举报
时间条件忘写了，呵呵
(select STCD,MAX(TM) AS TM,
max(Z)keep(dense_rank last order by TM) AS Z
FROM ST_RIVER_R
WHERE TM>to_date('2011-08-11','yyyy-mm-dd')
AND TM<=to_date('2011-08-14','yyyy-mm-dd')
GROUP BY STCD
) Zbw555 2014-08-12
- 打赏
- 举报
耗时最多的应该是not exists 部分，考虑使用分析函数
(SELECT * FROM ST_RIVER_R A
WHERE (NOT EXISTS(SELECT * FROM ST_RIVER_R C WHERE STCD=A.STCD AND TM>A.TM
AND TM>to_date('2011-08-11','yyyy-mm-dd')
AND TM<=to_date('2011-08-14','yyyy-mm-dd')))
AND A.TM>to_date('2011-08-11','yyyy-mm-dd')
AND A.TM<=to_date('2011-08-14','yyyy-mm-dd')) Z(select STCD,MAX(TM) AS TM,
max(Z)keep(dense_rank last order by TM) AS Z
FROM ST_RIVER_R
GROUP BY STCD
) Z
linxi246 2014-08-12
- 打赏
- 举报
SELECT T.LGTD,T.LTTD,Z.STCD,T.STNM,T.ADDVCD,'ZZ' STTP,T.FRGRD,Z.TM,Z.Z VALUE
FROM
(SELECT * FROM ST_RIVER_R A
WHERE (NOT EXISTS(SELECT * FROM ST_RIVER_R C WHERE STCD=A.STCD AND TM>A.TM
AND TM>to_date('2011-08-11','yyyy-mm-dd')
AND TM<=to_date('2011-08-14','yyyy-mm-dd')))
AND A.TM>to_date('2011-08-11','yyyy-mm-dd')
AND A.TM<=to_date('2011-08-14','yyyy-mm-dd')) Z,
(SELECT * FROM ST_STBPRP_B B
WHERE
B.STTP IN('ZZ','ZQ','PP','DD')
AND B.LGTD IS NOT NULL
AND B.LTTD IS NOT NULL
AND B.ADDVCD IS NOT NULL) T
WHERE Z.STCD=T.STCD;Plan hash value: 2587998097
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10561 | 2031K| 3614 (2)| 00:00:44 |
|* 1 | HASH JOIN RIGHT ANTI | | 10561 | 2031K| 3614 (2)| 00:00:44 |
|* 2 | TABLE ACCESS FULL | ST_RIVER_R | 11117 | 293K| 3060 (2)| 00:00:37 |
| 3 | NESTED LOOPS | | | | | |
| 4 | NESTED LOOPS | | 11117 | 1845K| 553 (0)| 00:00:07 |
|* 5 | TABLE ACCESS FULL | ST_STBPRP_B | 540 | 70200 | 13 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | STCD_TM1 | 42 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| ST_RIVER_R | 21 | 840 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("STCD"="A"."STCD")
filter("TM">"A"."TM")
2 - filter("TM">TO_DATE(' 2011-08-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"TM"<=TO_DATE(' 2011-08-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
5 - filter("B"."LGTD" IS NOT NULL AND "B"."LTTD" IS NOT NULL AND "B"."ADDVCD" IS
NOT NULL AND ("B"."STTP"='DD' OR "B"."STTP"='PP' OR "B"."STTP"='ZQ' OR
"B"."STTP"='ZZ'))
6 - access("A"."STCD"="B"."STCD" AND "A"."TM">TO_DATE(' 2011-08-11 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "A"."TM"<=TO_DATE(' 2011-08-14 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
-----------------------------------------------------------
6 CPU used by this session
8 CPU used when call started
128 DB time
1 enqueue releases
1 enqueue requests
189 non-idle wait count
113 non-idle wait time
6 opened cursors cumulative
1 opened cursors current
178 physical read total IO requests
162 physical read total multi block requests
1 pinned cursors current
10 recursive calls
1 recursive cpu usage
14997 session logical reads
131072 session pga memory
458416 session uga memory
113 user I/O wait time
6 user callsbw555 2014-08-12
- 打赏
- 举报
提供下执行计划吧，针对性的优化可能效果更好点
linxi246 2014-08-12
- 打赏
- 举报
好的，我试试～[/quote]
时间依然是1秒以上，没有多少提高～～
linxi246 2014-08-12
- 打赏
- 举报
好的，我试试～
linxi246 2014-08-12
- 打赏
- 举报
SELECT B.LGTD,B.LTTD,A.STCD,B.STNM,B.ADDVCD,'ZZ' STTP,B.FRGRD,A.TM,A.Z VALUE
FROM ST_RIVER_R A,ST_STBPRP_B B
WHERE
(NOT EXISTS(SELECT * FROM ST_RIVER_R WHERE STCD=A.STCD AND TM>A.TM AND TM>to_date('2011-08-11','yyyy-mm-dd') AND TM<=to_date('2011-08-14','yyyy-mm-dd')))
AND A.TM>to_date('2011-08-11','yyyy-mm-dd') AND TM<=to_date('2011-08-14','yyyy-mm-dd') AND A.STCD=B.STCD
AND B.STTP IN('ZZ','ZQ','PP','DD') AND B.LGTD IS NOT NULL
AND B.LTTD IS NOT NULL AND B.ADDVCD IS NOT NULL;SELECT D.LGTD,D.LTTD,C.STCD,D.STNM,D.ADDVCD,'ZZ' STTP,D.FRGRD,C.TM,C.Z VAL
FROM
(select stcd,max(tm) maxtm from ST_RIVER_R
where tm > to_date('2011-08-11','yyyy-mm-dd') AND TM<=to_date('2011-08-14','yyyy-mm-dd')
group by stcd
) B
inner join ST_RIVER_R C ON B.STCD = C.STCD
left join ST_STBPRP_B D ON C.STCD = D.STCD
where B.maxtm = C.TM and D.STTP IN('ZZ','ZQ','PP','DD') AND D.LGTD IS NOT NULL
AND D.LTTD IS NOT NULL AND D.ADDVCD IS NOT NULL;这个是有表名的SQL
ST_STBPRP_B 表结构截图
ST_RIVER_R 表结构截图
bw555 2014-08-12
- 打赏
- 举报
先试下这个
SELECT B.LGTD,B.LTTD,A.STCD,B.STNM,B.ADDVCD,'ZZ' STTP,B.FRGRD,A.TM,A.Z VALUE
FROM
(SELECT * FROM A
WHERE (NOT EXISTS(SELECT * FROM C WHERE STCD=A.STCD AND TM>A.TM
AND TM>to_date('2011-08-11','yyyy-mm-dd')
AND TM<=to_date('2011-08-14','yyyy-mm-dd')))
AND A.TM>to_date('2011-08-11','yyyy-mm-dd')
AND A.TM<=to_date('2011-08-14','yyyy-mm-dd')) A
(SELECT * FROM B
WHERE
AND B.STTP IN('ZZ','ZQ','PP','DD')
AND B.LGTD IS NOT NULL
AND B.LTTD IS NOT NULL
AND ADDVCD IS NOT NULL) B
WHERE A.STCD=B.STCD
小灰狼W 2014-08-12
- 打赏
- 举报
请提供执行计划、涉及表和字段的相关信息
另外，如果原表名因为保密关系需要隐藏，请用统一的名字代替。你这里明显ABCD都对不上
linxi246 2014-08-12
- 打赏
- 举报
由于是业务数据库，只有查询的权限，所以更改表结构应该做不到～～
linxi246 2014-08-12
- 打赏
- 举报
非常感谢，效率有很大的提升......
小灰狼W 2014-08-12
- 打赏
- 举报
ST_RIVER_R做全表扫描的成本较高
在这个表上建组合索引(STCD,TM,Z)，代替原有的(stcd,tm)
语句的话，将原来那条和楼上的写法来比较一下
