先试下这个 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 如果还慢的话,就需要看执行计划针对性的优化
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;这个是有表名的SQLST_STBPRP_B 表结构截图 ST_RIVER_R 表结构截图
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
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 calls
耗时最多的应该是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 试一下
时间条件忘写了,呵呵 (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 ) Z
另外,如果原表名因为保密关系需要隐藏,请用统一的名字代替。你这里明显ABCD都对不上
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
如果还慢的话,就需要看执行计划针对性的优化
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;这个是有表名的SQLST_STBPRP_B 表结构截图
ST_RIVER_R 表结构截图
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 calls
(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
试一下
(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
) Z
在这个表上建组合索引(STCD,TM,Z),代替原有的(stcd,tm)语句的话,将原来那条和楼上的写法来比较一下