首页 > 代码库 > Oracle 中, 使用 Instr 函数 替换 OR
Oracle 中, 使用 Instr 函数 替换 OR
简述
表结构
表 A -
A_SEQ_ID,
LVL1_NODE_ID,
LVL2_NODE_ID,
LVL3_NODE_ID,
LVL4_NODE_ID,
LVL5_NODE_ID,
LVL6_NODE_ID,
LVL7_NODE_ID,
LVL8_NODE_ID,
LVL9_NODE_ID,
LVL10_NODE_ID
表 B -
B_SEQ_ID,
NODE_ID,
INFO
开始时的 sql
SELECT * FROM A, BWHERE A.LVL1_NODE_ID = B.NODE_ID OR A.LVL2_NODE_ID = B.NODE_ID OR A.LVL3_NODE_ID = B.NODE_ID OR A.LVL4_NODE_ID = B.NODE_ID OR A.LVL5_NODE_ID = B.NODE_ID OR A.LVL6_NODE_ID = B.NODE_ID OR A.LVL7_NODE_ID = B.NODE_ID OR A.LVL8_NODE_ID = B.NODE_ID OR A.LVL9_NODE_ID = B.NODE_ID OR A.LVL10_NODE_ID = B.NODE_ID;
这条 sql虽然可以达到最终的目的,但是由于表 A和表 B的数据量比较大,所以执行起来相当慢。
使用 Instr函数
SELECT * FROM A, BWHERE instr( (‘,‘||A.LVL1_NODE_ID||‘,‘||A.LVL2_NODE_ID||‘,‘||A.LVL3_NODE_ID|| ‘,‘||A.LVL4_NODE_ID||‘,‘||A.LVL5_NODE_ID||‘,‘||A.LVL6_NODE_ID|| ‘,‘||A.LVL7_NODE_ID||‘,‘||A.LVL8_NODE_ID||‘,‘||A.LVL9_NODE_ID|| ‘,‘||A.LVL10_NODE_ID), ‘,‘||B.NODE_ID||‘,‘) > 0;
比起 OR语句一个字段一个字段的比较,instr函数更加高效。当 instr函数匹配到子串的时候就会返回子串在源字符串中的位置,所以这里用 大于0 即表示在表 A的源字符串中可以找到表 B的 NODE_ID (子串或源字符串为 NULL时返回 NULL)。
注:给每个字段加上逗号(‘,‘)的原因是匹配的一种方法,例如源数据是 1,2,3,13. 子串是 23.如果直接拼接的话,源字符串就变成 ‘12313‘,用instr(‘12313‘, ‘23‘) 明显可以匹配成功,但事实并非如此。所以换成给每个字符两边都加上逗号,不仅匹配字符也匹配其两边的逗号 - instr(‘,1,2,3,13,‘, ‘23‘)。
Oracle Instr 函数
其中:
INSTR 接受 characters格式的输入字符集,返回 characters格式的子串位置,位置索引从 1开始;
INSTRB 使用 bytes 而非 characters;
INSTRC 使用 Unicode complete characters;
INSTR2 使用UCS2 code points;
INSTR4 使用UCS4 code points。
对于源字符串,除了INSTRC, INSTR2, 和 INSTR4 不允许CLOB 和 NCLOB 类型外,其他两个函数的源字符串接受CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB 或 NCLOB等任意数据类型。
instr 语法如下: instr( string1, string2, start_position,nth_appearance )
string1 | 源字符串,要在此字符串中查找。 |
string2 | 要在string1中查找的字符串 。 |
start_position | 代表string1 的哪个位置开始查找。此参数可选,如果省略默认为1. 字符串索引从1开始。如果此参数为正,从左到右开始检索,如果此参数为负,从右到左检索,返回要查找的字符串在源字符串中的开始索引。 |
nth_appearance | 代表要查找第几次出现的string2. 此参数可选,如果省略,默认为 1.如果为负数系统会报错。 |
注意:
如果String2在String1中没有找到,instr函数返回0。
示例:
SELECT instr(‘syranmo‘,‘s‘) FROM dual; -- 返回 1
SELECT instr(‘syranmo‘,‘ra‘) FROM dual; -- 返回 3
SELECT instr(‘syran mo‘,‘a‘,1,2) FROM dual; -- 返回 0
参考资料
Oracle® Database SQL Language Reference 11g Release 2 (11.2)
- instr函数
Oracle 中, 使用 Instr 函数 替换 OR