Try to avoid apex_string.split in queries whenever performance is important.
Recently I modified a query from using apex_string.split to using instr, and it was 20 times faster after that.

The example below gives you an idea how that looks (use e.g. "10:20" for :P_DEPTNO) - but of course you need a more complex query to notice any performance changes: select e.empno , e.deptno from emp e where 1=1 and e.deptno in ( select column_value from table(apex_string.split(nvl(:P_DEPTNO, e.deptno), ':')) ) -- slow and ( instr( ':' || :P_DEPTNO || ':', ':' || e.deptno || ':' ) > 0 or :P_DEPTNO is null ) -- much faster

Two solutions for a SQL version of the apex_string.split (or apex_util.string_to_table in older Apex versions) PL/SQL function, for both strings and numbers.
I needed a pure SQL version to create insert statements with; of course I could have used a PL/SQL procedure but this was a lot simpler. And it performs very well.
Change the : to whatever you need, and remove the distinct if needed.

Note that apex_util.string_to_table was only introduced with Apex 5.1 (and apex_string.split in a later version), so I have also include the "OLD" code sections for older versions which use xmltable (and work just as well, but may be slower).

Region

Identification
	Sequence	-979
	Title	Listagg (reversed) for strings
	Type	Report
Source
	Source Type	SQL_QUERY
	Region Source	with t as ( select 'A:B:C:D:E' as col from dual union all select 'B:C:D:E:F:G' as col from dual union all select 'D:E:F' as col from dual ) select distinct t2.column_value as value from t cross join table(apex_string.split(t.col, ':')) t2 order by 1 /* OLD: select distinct extract(column_value, '//text()').getStringVal() as value from t , xmltable( replace( '''' || t.col || '''', ':', ''', ''') ) order by 1 */

Region

Identification
	Sequence	-969
	Title	Listagg (reversed) for numbers
	Type	Report
Source
	Source Type	SQL_QUERY
	Region Source	with t as ( select '1:2:3:4:5' as col from dual union all select '2:3:4:5:6:7' as col from dual union all select '4:5:6' as col from dual ) select distinct to_number(t2.column_value) as value from t cross join table(apex_string.split(t.col, ':')) t2 order by 1 /* OLD: select distinct to_number( extract(column_value, '//text()').getStringVal() ) as value from t , xmltable( replace( t.col, ':', ',') ) order by 1 */