有a,b两表,
a表字段xlh,bdsj,都是varchar2类型,数据量500万
b表字段xlh,bdsj,都是varchar2类型,数据量100万
两个字段都是数字,xlh是唯一,bdsj是时间转换为varchar2类型,如20110101010101。
两个表的bdsj都有索引问题:
这个语句:
select xlh,bdsj from a where bdsj>'20110101010101'---效率非常高,解释计划显示走索引,cost23
select xlh,bdsj from a where bdsj>20110101010101---效率非常低,解释计划显示不走索引,cost22万多select max(bdsj) from b ---效率比较高,index full scan,cost 8800select xlh,bdsj from a where bdsj>(select max(bdsj) from b)---效率非常低
怎么写才能让select xlh,bdsj from a where bdsj>(select max(bdsj) from b)这个语句像select xlh,bdsj from a where bdsj>'20110101010101'一样快?
a表字段xlh,bdsj,都是varchar2类型,数据量500万
b表字段xlh,bdsj,都是varchar2类型,数据量100万
两个字段都是数字,xlh是唯一,bdsj是时间转换为varchar2类型,如20110101010101。
两个表的bdsj都有索引问题:
这个语句:
select xlh,bdsj from a where bdsj>'20110101010101'---效率非常高,解释计划显示走索引,cost23
select xlh,bdsj from a where bdsj>20110101010101---效率非常低,解释计划显示不走索引,cost22万多select max(bdsj) from b ---效率比较高,index full scan,cost 8800select xlh,bdsj from a where bdsj>(select max(bdsj) from b)---效率非常低
怎么写才能让select xlh,bdsj from a where bdsj>(select max(bdsj) from b)这个语句像select xlh,bdsj from a where bdsj>'20110101010101'一样快?
这个走索引没问题select xlh,bdsj from a where bdsj>20110101010101---效率非常低,解释计划显示不走索引,cost22万多
这个因为bdsj为varchar2类型,常量20110101010101为数字型,bdsj将被转换,所以不能使用上索引select max(bdsj) from b ---效率比较高,index full scan,cost 8800
这个走全索引扫描没有问题的select xlh,bdsj from a where bdsj>(select max(bdsj) from b)---效率非常低
查看一下你的系统使用的优化方式,如果是CHOOSE,可以尝试对a,b表生成统计数据使系统走基于cost的优化模式
或者使用hint强制使用索引试一下
所以LZ需要对后面的类型进行转化。select xlh,bdsj from a where bdsj>to_char(select max(bdsj) from b)大概是这个思路。
select xlh, bdsj
from a
where bdsj > (select to_char(to_number(max(bdsj))) from b)
INTO VAR
from bselect xlh,bdsj from a where bdsj>to_char(VAR);