Optimizing MEMBER OF with APEX_STRING.SPLIT

apex_string.split is a great utility that allows you to take a delimited list and make it queryable. Here’s a small example:

1
2
3
4
5
6
7
8
9
select column_value
from apex_string.split('John,Sally,Bob', ',')
;

COLUMN_VALUE
_______________
John
Sally
Bob

You can read more about using apex_string.split and how to parse CSVs here.

When I use the split function to filter a table I used to do an explicit join as shown below. There’s nothing wrong with this concept but it can be a bit clunky to read and write.

1
2
3
4
5
6
select e.*
from dual
join emp e on 1=1
join (select column_value job from apex_string.split('CLERK,MANAGER', ',')) x on 1=1
and x.job = e.job
;

Recently Stefan Dobre tweeted about how you can use value member of apex_string.split(...) to accomplish the same thing. Here’s an example:

1
2
3
4
5
select *
from emp e
where 1=1
and e.job member of apex_string.split('CLERK,MANAGER', ',')
;

Using member of is a nice clean solution however since apex_string.split is a PL/SQL function there’s the potential of a lot of context switching (between SQL and PL/SQL) that can slow your code down. To verify this I made a simple wrapper function which logs all the calls to the function:

1
2
3
4
5
6
7
8
9
10
11
12
13
create or replace function wrapper_split(
p_str in varchar2,
p_sep in varchar2)
return wwv_flow_t_varchar2
as
begin
logger.log('START', 'wrapper_split');
return apex_string.split(
p_str => p_str,
p_sep => p_sep
);
end wrapper_split;
/

Using the previous example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select *
from emp e
where 1=1
and e.job member of wrapper_split('CLERK,MANAGER', ',')
;

-- ...

-- Check Logger:
select count(1) cnt
from logger_logs_5_min
where scope = 'wrapper_split'
;

CNT
______
14

They’re 14 rows in the emp table and apex_string.split was called 14 times. When using this on larger tables it could have significant performance impacts. Thankfully there’s a simple solution: scalar subqueries.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select *
from emp e
where 1=1
and e.job member of (select wrapper_split('CLERK,MANAGER', ',') from dual)
;

-- ...

-- Check Logger:
select count(1) cnt
from logger_logs_5_min
where scope = 'wrapper_split'
-- Note extra predicate was added to only show logged records after the previous example
;

CNT
______
1

This subtle difference limits it to one PL/SQL call regardless of the size of the table it’s checking. If using the member of syntax don’t forget to also reference apex_string.split in a scalar subquery.