select count(*) from (select prop_no from indi_acct where call_no like'T%'
and in_date >='2008-01-01' and in_date<='2011-01-01' )a,
(select prop_no_f from lend_hist where call_no like 'T%'
and lend_date >='2008-01-01' and lend_date<='2011-01-01')b
where a.prop_no =b.prop_no_f//现在用PL/sql developer查要半个小时 oracle好像已默认为相关字段加了index
表“lend_hist”五百万条记录,表“indi_acct ”三百万条,求高手赐教,谢谢
and in_date >='2008-01-01' and in_date<='2011-01-01' )a,
(select prop_no_f from lend_hist where call_no like 'T%'
and lend_date >='2008-01-01' and lend_date<='2011-01-01')b
where a.prop_no =b.prop_no_f//现在用PL/sql developer查要半个小时 oracle好像已默认为相关字段加了index
表“lend_hist”五百万条记录,表“indi_acct ”三百万条,求高手赐教,谢谢
select count(*)
from lend_hist lh,indi_acct ia
where ia.prop_no=lh.prop_no_f
and lh.call_no like 'T%'
and ia.call_no like 'T%'
and lh.lend_date between '2008-01-01' and '2011-01-01'
and ia.in_date between '2008-01-01' and '2011-01-01'
好像有 like 的话就不用索引了。不过索引个数太多了就不知道咋弄了。 create index XXX on a(substr(call_no, 1,1));
--试试这样
select count(*)
from lend_hist lh,indi_acct ia
where ia.prop_no=lh.prop_no_f
and lh.call_no like 'T%'
and ia.call_no like 'T%'
and lh.lend_date between '2008-01-01' and '2011-01-01'
and ia.in_date between '2008-01-01' and '2011-01-01'
----这个对查询效率没什么提升
这个写法是为了走索引吧
建议保留
然后按2楼的方法加索引
建议楼主贴执行计划