select ehu_awb.*
from ehu_awb
where ehu_awb.cmp = 'BH'
and (origin_stn = 'BHHKG' or ccn_stn = 'BHHKG' or
ehu_awb.dest_stn = 'BHHKG')
and ehu_awb.awb_date <= '20110831'
and ehu_awb.stn = 'HKG'
ehu_awb这张表有100多个字段,所以数据量很大,不过仅仅查询4000多行,就提示超过100M的缓存了。如何优化,索引也建了,也rebuild了索引。效果还是一样。
光是对EHU_AWB,不加任何条件的全表扫描,仅仅8000多行数据,就需要用30秒左右。我用它导出水晶报表,至少要1 minute以上。无语,相当无语
from ehu_awb
where ehu_awb.cmp = 'BH'
and ehu_awb.stn = 'HKG'
and ehu_awb.awb_date <= '20110831'
and (origin_stn = 'BHHKG' or ccn_stn = 'BHHKG' or ehu_awb.dest_stn = 'BHHKG')
select ehu_awb.A,ehu_awb.B
from ehu_awb
where ehu_awb.cmp = 'BH'
and ehu_awb.stn = 'HKG'
and ehu_awb.awb_date <= '20110831'
and origin_stn = 'BHHKG'
union
select ehu_awb.A,ehu_awb.B
from ehu_awb
where ehu_awb.cmp = 'BH'
and ehu_awb.stn = 'HKG'
and ehu_awb.awb_date <= '20110831'
and ccn_stn = 'BHHKG'
union
select ehu_awb.A,ehu_awb.B
from ehu_awb
where ehu_awb.cmp = 'BH'
and ehu_awb.stn = 'HKG'
and ehu_awb.awb_date <= '20110831'
and ehu_awb.dest_stn = 'BHHKG'如果没有给查询条件列建立索引, 就分别以origin_stn = 'BHHKG' ccn_stn = 'BHHKG' ehu_awb.dest_stn = 'BHHKG' 来查询, 数据少的放在查询条件左边。