SELECT *
FROM (SELECT t1.*,
(SELECT StationNum
FROM StationManageVillage
WHERE villageNum = t1.villageNum
AND ROWNUM = 1) AS stationNum,
(SELECT COUNT(*)
FROM taxpayerInfo
WHERE villageNum = t1.villageNum
AND StateN < 100
AND StateAuditN = 3
AND StateFinishN = 3
AND StateInManageN = 1) AS iNation,
(SELECT COUNT(*)
FROM taxpayerInfo
WHERE villageNum = t1.villageNum
AND StateL < 100
AND StateAuditL = 3
AND StateFinishL = 3
AND StateInManageL = 1) AS iLocal
FROM VillageData t1) t1
这个查询在SQL Server中执行效率很高(执行时间还不到1秒),可是到了oracle中却执行了30秒的时间。请帮忙优化一下这个查询。本人自己分析是在
(SELECT StationNum
FROM StationManageVillage
WHERE villageNum = t1.villageNum
AND ROWNUM = 1) AS stationNum,
(SELECT COUNT(*)
FROM taxpayerInfo
WHERE villageNum = t1.villageNum
AND StateN < 100
AND StateAuditN = 3
AND StateFinishN = 3
AND StateInManageN = 1) AS iNation,
(SELECT COUNT(*)
FROM taxpayerInfo
WHERE villageNum = t1.villageNum
AND StateL < 100
AND StateAuditL = 3
AND StateFinishL = 3
AND StateInManageL = 1) AS iLocal
这个几个查询里面用到了最底层中的VillageData t1 表导致执行速度忙。
FROM (SELECT t1.*,
(SELECT StationNum
FROM StationManageVillage
WHERE villageNum = t1.villageNum
AND ROWNUM = 1) AS stationNum,
(SELECT COUNT(*)
FROM taxpayerInfo
WHERE villageNum = t1.villageNum
AND StateN < 100
AND StateAuditN = 3
AND StateFinishN = 3
AND StateInManageN = 1) AS iNation,
(SELECT COUNT(*)
FROM taxpayerInfo
WHERE villageNum = t1.villageNum
AND StateL < 100
AND StateAuditL = 3
AND StateFinishL = 3
AND StateInManageL = 1) AS iLocal
FROM VillageData t1) t1
这个查询在SQL Server中执行效率很高(执行时间还不到1秒),可是到了oracle中却执行了30秒的时间。请帮忙优化一下这个查询。本人自己分析是在
(SELECT StationNum
FROM StationManageVillage
WHERE villageNum = t1.villageNum
AND ROWNUM = 1) AS stationNum,
(SELECT COUNT(*)
FROM taxpayerInfo
WHERE villageNum = t1.villageNum
AND StateN < 100
AND StateAuditN = 3
AND StateFinishN = 3
AND StateInManageN = 1) AS iNation,
(SELECT COUNT(*)
FROM taxpayerInfo
WHERE villageNum = t1.villageNum
AND StateL < 100
AND StateAuditL = 3
AND StateFinishL = 3
AND StateInManageL = 1) AS iLocal
这个几个查询里面用到了最底层中的VillageData t1 表导致执行速度忙。
我的Java群:37204596
我的SQL Server群:13433748
我的Oracle群:237204725