Rewrite NOT IN, but subquery involves a comma seperated string (ID)

Question

Changing Oracle stored procedure using SQL Developer.

Input: A comma separated IDs. (Example: 'P23,P37,P39,P45') Edit: please note that the input is a string, not an array of string. Also the string could be more than just 4 IDs. Could go up to about 200.

Want to find out from the table that does NOT have those input IDs.

The following is too slow. Just about 300 rows of data (in table) but takes about 20 seconds. So I want to rewrite. Please give me some hints on how to do.

ID_Array is '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;

Some of you may know it already. The following

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

will turn ID_Array into a table (table-like structure?) with four rows:

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

How long does it take if you hard-code it: `AND StudentID NOT IN ('P23','P37','P39','P45') AND Height <= 150` ? What do the execution plans for both show? Are your stats up-to-date? — Alex Poole, Jul 28 '17 at 11:06
As you're operating on a string rather than a table there's very little difference between the various methods of splitting a comma delimited string. I'd assume that there's something else wrong as Alex suggested. — Ben, Jul 28 '17 at 12:25
I hard code the string. The execution plan: cardinality: 184, and cost: 191 using _real_ data. It has a full table scan on StudentInfo. Using the answer from below, both execution plan is extermely low (like cost: 5). — Just a HK developer, Jul 31 '17 at 01:45

score 2 · Accepted Answer · answered Jul 28 '17 at 16:38

2

Your ID_Array must be a lot longer than your example here. I get very good performance with 'P23,P37,P39,P45'.

With longer strings, REGEXP_SUBSTR can get pretty slow. I'd suggest using LIKE instead whenever possible, even if it gets weird. Try this one.

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

answered Jul 28 '17 at 16:38

kfinity

8,581
1
13
20

Yes the ID_Array is a lot longer. The time taken is a bit less than David's answer, so I 'tick' yours to be the accepted answer. Thanks. – Just a HK developer Jul 31 '17 at 01:47

score 1 · Answer 2 · answered Jul 28 '17 at 17:25

There's no need to use CONNECT BY even if you're using a regular expression. You can either use LIKE or you can use REGEXP_LIKE():

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

I'm guessing id_array isn't short enough to be used as a regular expression pattern itself (300 rows?). If it were you could do the following:

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

But regular expression patterns in Oracle are limited, I think, to 512 bytes.

I actually get the first one working instead. The second one complains regular expression is too long. Thanks for your help. — Just a HK developer, Jul 31 '17 at 01:56

Rewrite NOT IN, but subquery involves a comma seperated string (ID)

2 Answers2