SELECT count(*)
FROM (DSAMERR201011) tb1
INNER JOIN sregioncode tb2 ON tb1.region_code like tb2.region_code || '%'
INNER JOIN tb_cde_kbp tb3 ON tb1.unit_id = tb3.kbp_class
inner join (select * from SLISTCONF where conf_type = '30001') tb5 on tb1.opr_status =
tb5.conf_value
left outer join(((select * from DSAMERR3201010)
union all (select * from DSAMERR3201011))) tb6 on tb1.ID = tb6.ID
left outer join (select * from SLISTCONF where conf_type = '30002') tb7 on tb6.opr_status =
tb7.conf_value
WHERE tb1.unit_id = '340-010-005-10-11'
AND tb1.region_code like '99%'
and 1 = 1
and 1 = 1
AND tb1.DATATIME = '20101104000000'
其中DSAMERR201011有300万记录,其他的都很小。为什么执行的效率极其的慢,大概需要4分钟。我修好成这样后SELECT count(*)
FROM (select * from DSAMERR201011 de WHERE de.unit_id = '340-010-005-10-11'
AND de.region_code = '99'
and 1 = 1
and 1 = 1
AND de.DATATIME = '20101104000000') tb1
left outer join(DSAMERR3201011) tb6 on tb1.ID = tb6.ID left outer join (select * from SLISTCONF where conf_type = '30002') tb7 on tb6.opr_status =
tb7.conf_value 其中tb1过滤后才91条记录,但是依然很慢。为什么呢?请教高手指点,感激不尽。
FROM (DSAMERR201011) tb1
INNER JOIN sregioncode tb2 ON tb1.region_code like tb2.region_code || '%'
INNER JOIN tb_cde_kbp tb3 ON tb1.unit_id = tb3.kbp_class
inner join (select * from SLISTCONF where conf_type = '30001') tb5 on tb1.opr_status =
tb5.conf_value
left outer join(((select * from DSAMERR3201010)
union all (select * from DSAMERR3201011))) tb6 on tb1.ID = tb6.ID
left outer join (select * from SLISTCONF where conf_type = '30002') tb7 on tb6.opr_status =
tb7.conf_value
WHERE tb1.unit_id = '340-010-005-10-11'
AND tb1.region_code like '99%'
and 1 = 1
and 1 = 1
AND tb1.DATATIME = '20101104000000'
其中DSAMERR201011有300万记录,其他的都很小。为什么执行的效率极其的慢,大概需要4分钟。我修好成这样后SELECT count(*)
FROM (select * from DSAMERR201011 de WHERE de.unit_id = '340-010-005-10-11'
AND de.region_code = '99'
and 1 = 1
and 1 = 1
AND de.DATATIME = '20101104000000') tb1
left outer join(DSAMERR3201011) tb6 on tb1.ID = tb6.ID left outer join (select * from SLISTCONF where conf_type = '30002') tb7 on tb6.opr_status =
tb7.conf_value 其中tb1过滤后才91条记录,但是依然很慢。为什么呢?请教高手指点,感激不尽。
查看你的执行计划,分析下再做:
EXPLAIN PLAN FOR SELECT .....
SELECT * FROM TABLE(dbms_xplan.display);