ORACLE LENTH 不用到索引解决方法

ORACLE LENTH 不用到索引

SQL code

select b.policycode,b.casenumber,1 as total,'B1' as standard, getdate() as aa,'C' as  grade from casemessage_ys b,policy awhere a.policycode =b.policycode and a.insuredname = b.casedriverand b.hldtype='2' and lenth(a.insuredname)>=2 and lenth(a.insuredname)<=4and b.isdown='0' and b.isstate='1'  and (b.link='核赔' or b.link='已决')

把条件and len(a.insuredname)>=2 and len(a.insuredname)<=4去掉的话，执行就用到索引很快，在1秒内，
但是有了LENTH之后，要执行很久。 用了LENTH就不能索引，怎么解决

[解决办法]
lenth(a.insuredname)建立一个函数索引
[解决办法]
create index fbi on sale_contacts (lenth(surname));

？？？
[解决办法]
and len(a.insuredname)>1 and len(a.insuredname)<5

这样呢？

查看更多 下一篇