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的情况，结果也同样类似。

至此。防。