Well that's a loaded title! Most developers may not know what each term means and as such this article will break down each item then group it all together.
apex_string.split
apex_string.split allows you to split a delimited string into rows (similar procedures exists for numbers and clobs).
select s.column_value
from apex_string.split('emp,dept', ',') s
;
COLUMN_VALUE
emp
dept
Pipelined Functions
Pipelined functions are PL/SQL functions that "pipe rows" and are selectable as a table in a SQL. apex_string.split is a pipelined function. Oracle Base has a great article all about pipelined table functions which you're encouraged to read and learn more about.
Pickler Fetch
You may have seen COLLECTION ITERATOR PICKLER FETCH in an Oracle explain plan but never understood what they are. Martin Widlake has a great post about this here. TL;DR Oracle is processing something of unknown number of rows and needs to provide the optimizer with a number of rows. It defaults to 8168 rows.
apex_string.split, Pipelined Functions, and Pickler Fetches
Putting it altogether now. When using apex_string.split in your queries you could be introducing a performance problem without even realize it. The following example highlights this.
create table all_objs as
select object_name
from all_objects
;
alter session set optimizer_capture_sql_plan_baselines=true;
select ao.object_name
from all_objs ao
join apex_string.split('EMP,DEPT',',') s on 1=1
and s.column_value = ao.object_name
;
select plan_table_output
from table(dbms_xplan.display_cursor(null,null,'typical'))
;
SQL_ID cq8vm0pb4jacn, child number 0
select ao.object_name from all_objs ao join
apex_string.split('EMP,DEPT',',') s on 1=1 and s.column_value =
ao.object_name
Plan hash value: 1741181693
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 112 (100)| |
|* 1 | HASH JOIN | | 13550 | 529K| 112 (1)| 00:00:01 |
| 2 | JOIN FILTER CREATE | :BF0000 | 8168 | 16336 | 29 (0)| 00:00:01 |
| 3 | COLLECTION ITERATOR PICKLER FETCH| SPLIT | 8168 | 16336 | 29 (0)| 00:00:01 |
| 4 | JOIN FILTER USE | :BF0000 | 46481 | 1724K| 82 (0)| 00:00:01 |
|* 5 | TABLE ACCESS STORAGE FULL | ALL_OBJS | 46481 | 1724K| 82 (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 - access("AO"."OBJECT_NAME"=VALUE(KOKBF$))
5 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"AO"."OBJECT_NAME"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"AO"."OBJECT_NAME"))
The plan from the example above highlights that the overall cardinality estimate is 13550 rows.
If you know the approximate number of items in the apex_string.split then a SQL hint can be applied to the query to tell the optimizer what to expect. The following demo highlights this. Using the previous example, it is a known fixed list of two items.
select
ao.object_name
from all_objs ao
join apex_string.split('EMP,DEPT',',') s on 1=1
and s.column_value = ao.object_name
;
select plan_table_output
from table(dbms_xplan.display_cursor(null,null,'typical'));
SQL_ID 80h36ywn1rjjn, child number 0
select
ao.object_name from all_objs ao join
apex_string.split('EMP,DEPT',',') s on 1=1 and s.column_value =
ao.object_name
Plan hash value: 1741181693
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 111 (100)| |
|* 1 | HASH JOIN | | 3 | 120 | 111 (0)| 00:00:01 |
| 2 | JOIN FILTER CREATE | :BF0000 | 2 | 4 | 29 (0)| 00:00:01 |
| 3 | COLLECTION ITERATOR PICKLER FETCH| SPLIT | 2 | 4 | 29 (0)| 00:00:01 |
| 4 | JOIN FILTER USE | :BF0000 | 46481 | 1724K| 82 (0)| 00:00:01 |
|* 5 | TABLE ACCESS STORAGE FULL | ALL_OBJS | 46481 | 1724K| 82 (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 - access("AO"."OBJECT_NAME"=VALUE(KOKBF$))
5 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"AO"."OBJECT_NAME"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"AO"."OBJECT_NAME"))
drop table all_objs;
The key difference in this that the optimizer now expects 3 rows (in the HASH JOIN row) instead of 13550! When doing more complex joins this can really improve performance.
In an APEX form where the user may be selecting a several objects I tend to use orders of magnitude estimate (ex: 10, 100, 1000) for a hint.
A few key notes about SQL hints:
Just because you can doesn't mean you should. Use then when necessary (and in the above example it is justified as the optimizer has no way of knowing the expected number of rows)
Good intro article about SQL hints by David Njoku here