您的位置: 首页  >  IT文章  >  Oracle 中， 使用 Instr 函数 替换 OR 简述 表结构 开始时的 sql 使用 Instr函数 Oracle Instr 函数  参考资料

Oracle 中， 使用 Instr 函数 替换 OR 简述 表结构 开始时的 sql 使用 Instr函数 Oracle Instr 函数  参考资料

分类: IT文章 • 2023-11-07 19:51:20
今天在写 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, B
WHERE 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, B
WHERE 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 函数

 
Oracle 中， 使用 Instr 函数 替换 OR 简述 表结构 开始时的 sql 使用 Instr函数 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

 参考资料

相关推荐