性能问题：SQL优化，索引已经利用上，最终返回也不多，为什么还是很慢

cxx1997 2011-04-15 09:22:26

我需要对3个表进行操作
tblmo 记录数：6128508
tbllmreceive 记录数：16356875
tbliqcticket 记录数：3431667

其中tblmo经过条件（select mo.sscode sscode,mo.mocode mocode,mo.mostatus mostatus,mo.schplanstartdate schplanstartdate
from mes.tblmo mo where mo.orgid=170
and mo.mostatus in ( 'mostatus_open')
and mo.schplanstartdate>=20110113 and mo.schplanstartdate<=20110113 ）
限定后，数据量只有 10条，查询时间0.0344秒

加上 tbllmreceive 后
select mo.sscode sscode,mo.mocode mocode,mo.mostatus mostatus,mo.schplanstartdate schplanstartdate
from ( select mo.sscode sscode,mo.mocode mocode,mo.mostatus mostatus,mo.schplanstartdate schplanstartdate
from mes.tblmo mo where mo.orgid=170
and mo.mostatus in ( 'mostatus_open')
and mo.schplanstartdate>=20110113 and mo.schplanstartdate<=20110113 )mo
join mes.tbllmreceive rec
on rec.orgid=170 and mo.mocode=to_number(rec.sono)
分析：
SELECT STATEMENT, GOAL = ALL_ROWS Cost=10062 Cardinality=8 Bytes=336
HASH JOIN Cost=10062 Cardinality=8 Bytes=336
TABLE ACCESS BY INDEX ROWID Object owner=MES Object name=TBLMO Cost=430 Cardinality=1 Bytes=30
INDEX RANGE SCAN Object owner=MES Object name=IND_TBLMO_5 Cost=59 Cardinality=7281
INDEX FAST FULL SCAN Object owner=MES Object name=I_TBLLMRECEIVE_SONO Cost=9531 Cardinality=10465079 Bytes=125580948

因为我在 tbllmreceive 建立了 org、to_number(sono) 索引，所以查询都利用到索引了，但是
INDEX FAST FULL SCAN Object owner=MES Object name=I_TBLLMRECEIVE_SONO Cost=9531 Cardinality=10465079 Bytes=125580948
这个索引成本很高，返回93行数据，时间14秒

如果我再加上tbliqcticket 表
select mo.sscode sscode,mo.mocode mocode,mo.mostatus mostatus,mo.schplanstartdate schplanstartdate
from ( select mo.sscode sscode,mo.mocode mocode,mo.mostatus mostatus,mo.schplanstartdate schplanstartdate
from mes.tblmo mo where mo.orgid=170
and mo.mostatus in ( 'mostatus_open')
and mo.schplanstartdate>=20110113 and mo.schplanstartdate<=20110113 )mo
join mes.tbllmreceive rec
on rec.orgid=170 and mo.mocode=to_number(rec.sono)
join mes.tbliqcticket iqc
on iqc.orgid=170 and iqc.deliverybarcode=rec.deliverybarcode
分析：
SELECT STATEMENT, GOAL = ALL_ROWS Cost=76887 Cardinality=1 Bytes=66
NESTED LOOPS Cost=76887 Cardinality=1 Bytes=66
HASH JOIN Cost=76871 Cardinality=8 Bytes=416
TABLE ACCESS BY INDEX ROWID Object owner=MES Object name=TBLMO Cost=430 Cardinality=1 Bytes=30
INDEX RANGE SCAN Object owner=MES Object name=IND_TBLMO_5 Cost=59 Cardinality=7281
TABLE ACCESS FULL Object owner=MES Object name=TBLLMRECEIVE Cost=76340 Cardinality=10465079 Bytes=230231738
INDEX RANGE SCAN Object owner=MES Object name=IND_TBLIQCTICKET_ORGDB Cost=2 Cardinality=1 Bytes=14

可以看到 TBLLMRECEIVE 变成了全表扫描，tbliqcticket 在 orgid、deliverybarcode 上建立了索引
最终返回90行，96秒

所以就这个查询而已，如何优化呢？

...全文