先把这些查询字段建立索引a.ayear = b.ayear
and a.amonth = b.amonth
and a.cbzx = b.zd
and a.sfz = b.sfz
and a.mdz = b.mdz
and a.xm = b.xm
and a.fwlx = b.fwlx
and a.ayear = 2004
and a.amonth = 1这样才能提高查询速度,
语句是没有问题
and a.amonth = b.amonth
and a.cbzx = b.zd
and a.sfz = b.sfz
and a.mdz = b.mdz
and a.xm = b.xm
and a.fwlx = b.fwlx
and a.ayear = 2004
and a.amonth = 1这样才能提高查询速度,
语句是没有问题
a.ayear = b.ayear
and a.amonth = b.amonth
and a.cbzx = b.zd
and a.sfz = b.sfz
and a.mdz = b.mdz
and a.xm = b.xm
and a.fwlx = b.fwlx
and a.ayear = 2004
and a.amonth = 1
用小表作为基础表,也就是紧靠from的表.
——-------------------------------------
有些文章上说,后面的才是基础表,建议把小表放在后面。晕了~ 谁能给个准确的说法。
条件写在后面的是先执行的,这个我试过了。这个不知道怎么试
-------------------------------------
这种说法其实很不准确,放在后面的说法也不准确。有两种解决方法:
1.如果将表和索引都进行了统计分析,优化器模式选用的是choose,那么索引建立正确后数据库能自动选择效率比较高的执行计划。
2.在Select 后面加上/*+ ordered*/提示符,检索的顺序将按根据From字句中表的先后顺序进行,但是表的顺序不要与逻辑矛盾,否则提示无效。
a.amonth,
a.cbzx,
a.zylx,
a.zy,
a.hd,
a.kmlx,
a.kmsx,
a.cbkm,
sum(a.kmz),
sum(b.zl),
a.xzl
from (select * from TBL_RE_CPCBJGSJ where ayear=2004 and amonth=1) a, TBL_DATA_ZDCPHLB b
where a.ayear = b.ayear
and a.amonth = b.amonth
and a.cbzx = b.zd
and a.sfz = b.sfz
and a.mdz = b.mdz
and a.xm = b.xm
and a.fwlx = b.fwlx
group by a.ayear,
a.amonth,
a.cbzx,
a.zylx,
a.zy,
a.hd,
a.kmlx,
a.kmsx,
a.cbkm,
a.xzl