Oracle INSTR和LIKE转换注意事项
Oracle INSTR和LIKE转换注意事项
A式：INSTR(A.G_NO || ',', B.G_NO || ',') >= 1 和 B式：INSTR(A.G_NO , B.G_NO) >= 1 等价吗
不等价，
1、B.G_NO有空值的情况，
假设A.G_NO也为空值，则A式为','和','比较是成立的，即空值和空值是符合的，而B式则不符合
假设A.G_NO='XXXX'，则A式为'XXXX,'和','比较也是成立的，B式则不符合
14:40:12 SYS@test(36)> select instr(null,null) r1,instr(null||',',null||',') r2,instr('XXXX'||',',null||',') r3 from dual; R1 R2 R3 ---------- ---------- ---------- 1 5 Elapsed: 00:00:00.00
2、假设 A.G_NO='2,3,4,5,112'
B.G_NO='1'
A式左边=0，不成立，B式左边=9，成立
14:25:56 SYS@test(36)> select instr('2,3,4,5,112,','1,') A,instr('2,3,4,5,112','1') B from dual; A B ---------- ---------- 0 9 Elapsed: 00:00:00.00
也许可能存在其他情况。
A式：INSTR(A.G_NO || ',', B.G_NO || ',') >= 1 和 B式：A.G_NO LIKE '%' || B.G_NO || '%' 等价吗
不等价，同理，
B.G_NO有空值的情况，
假设A.G_NO也为空值，那么A式=INSTR(',',','),B式=null like '%%',明显不等价
假设A.G_NO='XXXX'，那么A式=INSTR('XXXX,',','),B式='XXXX' like '%%'
那么INSTR(A.G_NO || ',', B.G_NO || ',') >= 1和 A.G_NO LIKE '%' || B.G_NO || '%' AND B.G_NO IS NOT NULL 等价吗
也不等价，和上边例子的第二个举例一样。
只能将INSTR(A.G_NO || ',', B.G_NO || ',') >= 1改为： A.G_NO || ',' LIKE '%' || B.G_NO || ',%' AND B.G_NO IS NOT NULL。
什么，你问我instr有必要改为like吗？
现有一毛一样的测试表两个，如下：
15:11:56 ZKM@test(75)> select name,count(*) from a group by name; 15:12:05 ZKM@test(75)> select name,count(*) from b group by name; NAME COUNT(*) NAME COUNT(*) ------------------------- ---------- ------------------------- ---------- 10896 10896 abcd 2 abcd 2 abc 6 abc 6 Elapsed: 00:00:00.25 Elapsed: 00:00:00.39
https://www.cnblogs.com/PiscesCanon/p/18335898
测试比对的SQL和执行情况如下：
select count(*) from a,b where a.id=b.id and instr(a.name,b.name,1,1)>=1; Plan hash value: 1397777030 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:08.13 | 38 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:08.13 | 38 | | | | |* 2 | HASH JOIN | | 1 | 5944K| 52 |00:00:08.13 | 38 | 2168K| 2168K| 1411K (0)| | 3 | TABLE ACCESS FULL| A | 1 | 10904 | 10904 |00:00:00.01 | 19 | | | | | 4 | TABLE ACCESS FULL| B | 1 | 10904 | 10904 |00:00:00.01 | 19 | | | | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."ID"="B"."ID") filter(INSTR("A"."NAME","B"."NAME",1,1)>=1) select count(*) from a,b where a.id=b.id and a.name like b.name || '%' and a.name is not null and b.name is not null; ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 38 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 38 | | | | |* 2 | HASH JOIN | | 1 | 32 | 52 |00:00:00.01 | 38 | 1888K| 1888K| 621K (0)| |* 3 | TABLE ACCESS FULL| A | 1 | 8 | 8 |00:00:00.01 | 19 | | | | |* 4 | TABLE ACCESS FULL| B | 1 | 8 | 8 |00:00:00.01 | 19 | | | | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."ID"="B"."ID") filter("A"."NAME" LIKE "B"."NAME"||'%') 3 - filter("A"."NAME" IS NOT NULL) 4 - filter("B"."NAME" IS NOT NULL)
在HASH JOIN的前提下，在这里instr的速度要远低于like的速度，原因是参与HASH JOIN步骤的A表和B表的数据量不同，可以对比两个执行计划Id=3，4的'A-Rows‘就看出来了。
回来instr有必要改为like的问题来，也可以不改，不过如果存在涉及字段存在null值得情况最好加上is not null限制（虽然这里本身instr已经限制了null值），估计大多数人会忽略这个情况吧。
当然，如果是NL的情况，结果也同样类似。
至此。防。
