重写NOT IN，但子查询使用逗号分隔的字符串（ID）

问题描述：

使用SQL Developer更改Oracle存储过程。重写NOT IN，但子查询使用逗号分隔的字符串（ID）

输入：用逗号分隔的ID。（例如：'P23，P37，P39，P45'）编辑：请注意输入是一个字符串，不是的一个字符串数组。此外，字符串可能不仅仅是4个ID。可能高达约200.

想从表中找出没有这些输入ID的表。

以下是太慢了。只有大约300行数据（在表中），但大约需要20秒。所以我想重写。请给我一些关于如何去做的提示。

ID_Array是'P23，P37，P39，P45'。

SELECT * FROM StudentInfo 
WHERE StudentClass = 'Primary5A' 
AND StudentID NOT IN 
(
    SELECT REGEXP_SUBSTR(ID_Array, '[^,]+', 1, LEVEL) StudentID 
    FROM DUAL 
    CONNECT BY REGEXP_SUBSTR(ID_Array, '[^,]+', 1, LEVEL) IS NOT NULL 
) 
AND Height <= 150;

你们有些人可能已经知道了。下面

SELECT REGEXP_SUBSTR(ID_Array, '[^,]+', 1, LEVEL) StudentID 
    FROM DUAL 
    CONNECT BY REGEXP_SUBSTR(ID_Array, '[^,]+', 1, LEVEL) IS NOT NULL

会变成ID_Array为表（表样结构？）有四行：

+-----+ 
| P23 | 
| P37 | 
| P39 | 
| P45 | 
+-----+

如果您对其进行硬编码，需要多长时间：AND StudentID NOT IN（'P23'，'P37'，'P39'，'P45'）和高度

由于您使用的是字符串而不是表格，因此拆分逗号分隔字符串的各种方法之间几乎没有区别。我认为Alex的建议还有其他错误。 – Ben

我硬编码的字符串。执行计划：基数：184，成本：191使用_real_数据。它在StudentInfo上进行全表扫描。使用下面的答案，两个执行计划都非常低（如成本：5）。 – user3454439

答

你ID_Array一定比这里你的榜样长得多。 'P23，P37，P39，P45'的表现非常出色。

如果字符串较长，REGEXP_SUBSTR会变得非常慢。我建议尽可能使用LIKE，即使它变得奇怪。试试这个。

SELECT * FROM StudentInfo 
WHERE StudentClass = 'Primary5A' 
AND ','||ID_Array||',' NOT LIKE '%,'||StudentID||',%' 
AND Height <= 150;

是的，ID_Array时间更长。所花费的时间比戴维的回答要少一些，所以我打勾你的答案。谢谢。 – user3454439

答

即使使用正则表达式，也不需要使用CONNECT BY。您可以使用LIKE或者你可以使用REGEXP_LIKE()：

SELECT * FROM studentinfo 
WHERE studentclass = 'Primary5A' 
    AND height <= 150 
    AND NOT REGEXP_LIKE(','||id_array||',', ','||studentid||',');

我猜id_array不够短，作为一个正则表达式本身（300行？）。如果是，你可以做到以下几点：

SELECT * FROM studentinfo 
WHERE studentclass = 'Primary5A' 
    AND height <= 150 
    AND NOT REGEXP_LIKE(student_id, '^(' || REPLACE(id_array, ',', '|') || ')$');

但经常在甲骨文表达模式是有限的，我认为，为512个字节。

我实际上得到了第一个工作。第二个抱怨正则表达式太长。谢谢你的帮助。 – user3454439

是的，Oracle正则表达式被限制为512字节 –

重写NOT IN，但子查询使用逗号分隔的字符串（ID）

相关推荐