首页 > 代码库 > Oracle 中, 使用 Instr 函数 替换 OR

Oracle 中, 使用 Instr 函数 替换 OR

简述

今天在写 sql时遇到一个情况,表 A中的 ID 是按照 TREE结构存储的。现在需要和表 B中的 NODE_ID连接,取出 B中 NODE_ID可以和 A中任意一个 level的 NODE_ID连接的信息。但是表 B中的 NODE_ID 具体对应到表 A中哪个 level是未知的。对此,最先想到使用的是 OR运算,但是由于效率太低,速度很慢,后来使用 INSTR代替,查询速度得到明显提高。

表结构

表 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 函数

 
Description of instr.gif follows
 

其中:
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