优化、快速查询!!!

Question

select * from tbname where instr(col1||col2||...||col30,'str')>0;

Answer 1

这样有漏洞
select * from tbname where instr(col1,'str')+instr(col2,'str')+...+instr(col30,'str')>0;

Answer 2

这样还有漏洞
select * from tbname where instr(nvl(col1,'-1'),'str')+instr(nvl(col1,'-1'),'str')+...+instr(nvl(col1,'-1'),'str')>0;16:40:41 jlanzpa817>select instr(null,'1') from dual;INSTR(NULL,'1')
---------------
已用时间:  00: 00: 00.10
16:40:55 jlanzpa817>select null + 1 from dual;    NULL+1
----------
已用时间:  00: 00: 00.00

Answer 3

这样应该可以了
select * from tbname where nvl(instr(col1,'str'),0)+nvl(instr(col2,'str'),0)+...+nvl(instr(col30,'str'),0)>0;SQL> select * from tmp;        A         B         C
--------- --------- ---------
        2         6
      123       999       111
      222       888       222
      333       999       333SQL> select * from tmp where nvl(instr(a,'6'),0)+nvl(instr(b,'6'),0)+nvl(instr(c,'6'),0)>0;        A         B         C
--------- --------- ---------
        2         6