oracle - Rewrite NOT IN, but subquery involves a comma seperated string (ID) -
changing oracle stored procedure using sql developer.
input: comma separated ids. (example: 'p23,p37,p39,p45') edit: please note input string, not array of string. string more 4 ids. go 200.
want find out table not have input ids.
the following slow. 300 rows of data (in table) takes 20 seconds. want rewrite. please give me hints on how do.
id_array 'p23,p37,p39,p45'.
select * studentinfo studentclass = 'primary5a' , studentid not in ( select regexp_substr(id_array, '[^,]+', 1, level) studentid dual connect regexp_substr(id_array, '[^,]+', 1, level) not null ) , height <= 150;
some of may know already. following
select regexp_substr(id_array, '[^,]+', 1, level) studentid dual connect regexp_substr(id_array, '[^,]+', 1, level) not null
will turn id_array table (table-like structure?) 4 rows:
+-----+ | p23 | | p37 | | p39 | | p45 | +-----+
your id_array must lot longer example here. performance 'p23,p37,p39,p45'.
with longer strings, regexp_substr
can pretty slow. i'd suggest using like
instead whenever possible, if gets weird. try one.
select * studentinfo studentclass = 'primary5a' , ','||id_array||',' not '%,'||studentid||',%' , height <= 150;
Comments
Post a Comment