How efficient are they and what effect can they have on a plan?
I am using 11.2.0.3 for these tests
The function is created as follows:
CREATE OR REPLACE TYPE t_string_table IS TABLE OF VARCHAR2(32000);
/
show errors
CREATE OR REPLACE FUNCTION string_token(pi_input IN VARCHAR2,
pi_del IN VARCHAR2 DEFAULT ',')
RETURN t_string_table
PIPELINED
IS
l_idx PLS_INTEGER;
l_list VARCHAR2(32767) := pi_input;
l_value VARCHAR2(32767);
BEGIN
LOOP
l_idx := INSTR(l_list,pi_del);
IF l_idx > 0 THEN
pipe ROW(LTRIM(RTRIM(SUBSTR(l_list,1,l_idx-1))));
l_list := SUBSTR(l_list,l_idx+LENGTH(pi_del));
ELSE
pipe ROW(LTRIM(RTRIM(l_list)));
EXIT;
END IF;
END LOOP;
RETURN;
END string_token;
/
show errors
Now we are good to go. First thing to establish is what cardinality is estimated by Oracle when calling the pipelined function: I am passing in 5 values
var str VARCHAR2(4000);
var delim VARCHAR2(1);
exec :str := '10,11,12,13,14';
exec :delim := ',';
SELECT /*+ gather_plan_statistics monitor bind_aware */
FROM TABLE(string_token(:str,:delim));
Running the trace through TRCA outputs the following plan (edited to remove information about the reads):
ID PID Card Rows Row Source Operation
--- ---- ------ ------- -----------------------------------------------
1: 0 8168 5 COLLECTION ITERATOR PICKLER FETCH STRING_TOKEN
What about a more complex example?
I have created two tables which can be used for this test
exec dbms_random.seed(0);
create table from_table
pctfree 99
pctused 1
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <= 100
)
select
rownum id,
CASE WHEN MOD(rownum,10) < 5 THEN 4 ELSE MOD(rownum,10) END num,
lpad(rownum,10,'0') small_vc,
trunc(100 * dbms_random.normal) val,
rpad('x',100) padding
from
generator v1,
generator v2
where
rownum <= 1000;
CREATE UNIQUE INDEX i_from_table_01 ON from_table(id);
-- Gather stats
create table join_table
pctfree 99
pctused 1
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <= 1000
)
select
rownum id,
CASE WHEN MOD(rownum,10) < 5 THEN 4 ELSE MOD(rownum,10) END num,
lpad(rownum,10,'0') small_vc,
trunc(100 * dbms_random.normal) val,
rpad('x',100) padding,
(MOD(rownum, 1000) + 1) ft_id
from
generator v1,
generator v2
where
rownum <= 10000
;
CREATE UNIQUE INDEX i_join_table_01 ON join_table(id);
CREATE INDEX i_join_table_02 ON join_table(ft_id);
-- Gather stats
Now the table function is used to create a result set which joins to the from_table.
exec :str := '10,11,12,13,14';
exec :delim := ',';
SELECT /*+ gather_plan_statistics monitor bind_aware */
FROM from_table ft
JOIN TABLE(string_token(:str,:delim)) ON column_value = ft.id
JOIN join_table jt ON ft.id = jt.ft_id;
ID PID Card Rows Row Source Operation
--- ---- ------ ------- ------------------------------------------------
1: 0 81680 50 HASH JOIN
2: 1 8168 5 COLLECTION ITERATOR PICKLER FETCH STRING_TOKEN
3: 1 10000 10000 HASH JOIN
4: 3 1000 1000 . TABLE ACCESS FULL FROM_TABLE
5: 3 10000 10000 . TABLE ACCESS FULL JOIN_TABLE
In this case, Oracle assumes that the best way to get the data from the tables is by two full table scans - note that the discrepancy between the estimated Card (8168) and the actual returned (5).
Now add a cardinality hint saying that a handful of rows are returned. Note that I have rewritten the query to use an inline view.
exec :str := '10,11,12,13,14';
exec :delim := ',';
SELECT /*+ gather_plan_statistics monitor bind_aware */
FROM from_table ft
JOIN (SELECT /*+ cardinality(t 5) */* FROM TABLE(string_token(:str,:delim))t) ON column_value = ft.id
JOIN join_table jt ON ft.id = jt.ft_id;
ID PID Card Rows Row Source Operation
--- ---- ------ ------- -------------------------------------------------
1: 0 50 50 NESTED LOOPS
2: 1 5 5 NESTED LOOPS
3: 2 5 5 . COLLECTION ITERATOR PICKLER FETCH STRING_TOKEN
4: 2 1 5 . TABLE ACCESS BY INDEX ROWID FROM_TABLE
5: 4 1 5 .. INDEX UNIQUE SCAN I_FROM_TABLE_01
6: 1 10 50 TABLE ACCESS BY INDEX ROWID JOIN_TABLE
7: 6 10 50 . INDEX RANGE SCAN I_JOIN_TABLE_02
A far better plan.
To summarize, consider using the CARDINALITY hint to tell Oracle how many rows are expected to be returned when using pipelined functions where possible. It should lead to better plans being used for your queries.
