(SELECT pstn_connum, pstn_totalnum FROM
(SELECT (COUNT(DISTINCT cp.port_id)) pstn_connum
FROM connector_port cp, device de
WHERE de.sub_type = 2001
AND cp.device_id = de.id
AND EXISTS ( SELECT 1 FROM (SELECT DISTINCT t.tsbid adevice
FROM GIS.TECDSLH t
start with t.wsbid in
(SELECT DISTINCT fz.wsbid
FROM tedlfz fz ,device de WHERE fz.wsbid=de.id AND de.local_net_id =810 AND de.sub_type= 20022)
connect by nocycle prior t.tsbid = t.wsbid) t2 WHERE cp.connect_device_id=t2.adevice)) conn_port,
(SELECT COUNT(p.id) pstn_totalnum
FROM port p
WHERE --p.device_id IN
EXISTS (SELECT 1 from (
(SELECT de.id FROM device de
WHERE de.local_net_id =810
AND de.sub_type = 2001
AND P.DEVICE_ID=DE.ID)))))数据库sql优化
(SELECT (COUNT(DISTINCT cp.port_id)) pstn_connum
FROM connector_port cp, device de
WHERE de.sub_type = 2001
AND cp.device_id = de.id
AND EXISTS ( SELECT 1 FROM (SELECT DISTINCT t.tsbid adevice
FROM GIS.TECDSLH t
start with t.wsbid in
(SELECT DISTINCT fz.wsbid
FROM tedlfz fz ,device de WHERE fz.wsbid=de.id AND de.local_net_id =810 AND de.sub_type= 20022)
connect by nocycle prior t.tsbid = t.wsbid) t2 WHERE cp.connect_device_id=t2.adevice)) conn_port,
(SELECT COUNT(p.id) pstn_totalnum
FROM port p
WHERE --p.device_id IN
EXISTS (SELECT 1 from (
(SELECT de.id FROM device de
WHERE de.local_net_id =810
AND de.sub_type = 2001
AND P.DEVICE_ID=DE.ID)))))数据库sql优化
1.DISTINCT尽量少用,可以用EXISTS代替。
2.能直接连接查询,不要用EXISTS.
3.SQL可以一层层来构思(子查询),但写时需要把构思的层次整合一下(少用子查询)。
4.优化可以把执行计划贴出来,才能对症下药。(需要知道相关索引情况,设计的数据表的数据量)
5.按照表的数据量,调整索引执行
6.对于不走索引的语句,思考是否适合建立索引。
FROM (SELECT (COUNT(DISTINCT CP.PORT_ID)) PSTN_CONNUM
FROM CONNECTOR_PORT CP, DEVICE DE
WHERE DE.SUB_TYPE = 2001
AND CP.DEVICE_ID = DE.ID
AND EXISTS
(SELECT 1
FROM (SELECT DISTINCT T.TSBID ADEVICE
FROM GIS.TECDSLH T
START WITH T.WSBID IN
(SELECT DISTINCT FZ.WSBID
FROM TEDLFZ FZ, DEVICE DE
WHERE FZ.WSBID = DE.ID
AND DE.LOCAL_NET_ID = 810
AND DE.SUB_TYPE = 20022)
CONNECT BY NOCYCLE PRIOR T.TSBID = T.WSBID) T2
WHERE CP.CONNECT_DEVICE_ID = T2.ADEVICE)) CONN_PORT,
(SELECT COUNT(P.ID) PSTN_TOTALNUM
FROM PORT P
WHERE --p.device_id IN
EXISTS (SELECT 1
FROM ((SELECT DE.ID
FROM DEVICE DE
WHERE DE.LOCAL_NET_ID = 810
AND DE.SUB_TYPE = 2001
AND P.DEVICE_ID = DE.ID))));嵌入这么多子查询,还是建议分开查吧,写个过程片段处理。
如果非的一条sql实现,看看执行计划吧,贴出来,大家看看。
对别人尊重也是对自己负责。大家都很忙