So far in this tutorial we have only explored "Row Semantics" polymorphic table functions, i.e. those defined with a PIPELINED ROW clause. In this module we explore "Table Semantics" polymorphic table functions, i.e. those defined with a PIPELINED TABLE clause. Table Semantics PTFs allow us to specify PARTITION BY and ORDER BY clauses (like those available with Oracle analytic functions) when invoking a PTF.
Let's create both types of functions and take them for a spin.
create or replace package ptf_demo as
function describe( p_input_table_metadata in out dbms_tf.table_t )
return dbms_tf.describe_t ;
-- this is the Table Semantics PTF
function table_semantics( p_table table )
return table PIPELINED TABLE polymorphic using ptf_demo ;
-- we'll also create a row semantics version for comparison
function row_semantics( p_table table )
return table PIPELINED ROW polymorphic using ptf_demo ;
end ptf_demo ;
/
create or replace package body ptf_demo as
function describe( p_input_table_metadata in out dbms_tf.table_t )
return dbms_tf.describe_t as
begin
return null;
end describe ;
end ptf_demo;
/
Now we run both PTFs to see what they return.
select deptno, ename from ptf_demo.row_semantics( scott.emp );
DEPTNO ENAME
---------- ----------
10 KING
10 MILLER
10 CLARK
20 SMITH
20 JONES
20 ADAMS
20 FORD
20 SCOTT
30 ALLEN
30 WARD
30 MARTIN
30 BLAKE
30 TURNER
30 JAMES
select deptno, ename from ptf_demo.table_semantics( scott.emp );
DEPTNO ENAME
---------- ----------
10 KING
10 MILLER
10 CLARK
20 SMITH
20 JONES
20 ADAMS
20 FORD
20 SCOTT
30 ALLEN
30 WARD
30 MARTIN
30 BLAKE
30 TURNER
30 JAMES
Note how the output of both SELECT commands is the same.
Let's try using PARTITION BY and ORDER BY clauses with ROW_SEMANTICS().
select deptno, ename from ptf_demo.row_semantics( scott.emp PARTITION BY DEPTNO ORDER BY ENAME ) order by deptno, ename asc ;
Error starting at line : 1 in command - select deptno, ename from ptf_demo.row_semantics( scott.emp PARTITION BY DEPTNO ORDER BY ENAME ) order by deptno, ename asc Error at Command Line : 2 Column : 55 Error report - SQL Error: ORA-62568: invalid use of PARTITION BY or ORDER BY clause
It does not work with ROW_SEMANTICS(). We will have better luck with TABLE_SEMANTICS().
select deptno, ename from ptf_demo.table_semantics( scott.emp PARTITION BY DEPTNO ORDER BY ENAME DESC ) order by deptno, ename asc ;
DEPTNO ENAME
---------- ----------
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD
In the absence of any useful FETCH_ROWS() logic the output is unremarkable however. Let's continue exploring.
PARTITION BY
To better understand how the PARTITION BY clause affects execution we take a look at what happens internally.
create or replace package ptf_demo as
function describe( p_input_table_metadata in out dbms_tf.table_t )
return dbms_tf.describe_t ;
procedure fetch_rows ;
function table_semantics( p_table table )
return table PIPELINED TABLE polymorphic using ptf_demo ;
function row_semantics( p_table table )
return table PIPELINED ROW polymorphic using ptf_demo ;
end ptf_demo ;
/
create or replace package body ptf_demo as
function describe(
p_input_table_metadata in out dbms_tf.table_t )
return
dbms_tf.describe_t
as
v_new_column_desc dbms_tf.describe_t ;
begin
--
-- make ENAME available to get_row_set()
--
p_input_table_metadata.column(2).for_read := true ;
--
-- create a new output column
--
v_new_column_desc.new_columns(1).name := 'INTROSPECTION' ;
v_new_column_desc.new_columns(1).type := dbms_tf.type_varchar2 ;
return v_new_column_desc ;
end describe ;
procedure fetch_rows as
v_input_column dbms_tf.tab_varchar2_t ;
v_output_column dbms_tf.tab_varchar2_t ;
i pls_integer ;
begin
dbms_tf.get_col( 1, v_input_column );
for i in 1 .. dbms_tf.get_env().row_count loop
v_output_column(i) :=
'Row Count = ' || dbms_tf.get_env().row_count || ', ' ||
'First ENAME in the row set = ' || v_input_column(1) ;
end loop ;
dbms_tf.put_col( 1, v_output_column );
end fetch_rows ;
end ptf_demo ;
/
select deptno, ename, introspection from ptf_demo.row_semantics( scott.emp ) ;
DEPTNO ENAME INTROSPECTION
---------- ---------- ------------------------------------------------------------
10 KING Row Count = 14, First ENAME in the row set = KING
10 MILLER Row Count = 14, First ENAME in the row set = KING
10 CLARK Row Count = 14, First ENAME in the row set = KING
20 SMITH Row Count = 14, First ENAME in the row set = KING
20 JONES Row Count = 14, First ENAME in the row set = KING
20 ADAMS Row Count = 14, First ENAME in the row set = KING
20 FORD Row Count = 14, First ENAME in the row set = KING
20 SCOTT Row Count = 14, First ENAME in the row set = KING
30 ALLEN Row Count = 14, First ENAME in the row set = KING
30 WARD Row Count = 14, First ENAME in the row set = KING
30 MARTIN Row Count = 14, First ENAME in the row set = KING
30 BLAKE Row Count = 14, First ENAME in the row set = KING
30 TURNER Row Count = 14, First ENAME in the row set = KING
30 JAMES Row Count = 14, First ENAME in the row set = KING
The output above tells us that FETCH_ROWS() is only called once when executing ROW_SEMANTICS().
select deptno, ename, introspection from ptf_demo.table_semantics( scott.emp ) ;
DEPTNO ENAME INTROSPECTION
---------- ---------- ------------------------------------------------------------
10 KING Row Count = 14, First ENAME in the row set = KING
10 MILLER Row Count = 14, First ENAME in the row set = KING
10 CLARK Row Count = 14, First ENAME in the row set = KING
20 SMITH Row Count = 14, First ENAME in the row set = KING
20 JONES Row Count = 14, First ENAME in the row set = KING
20 ADAMS Row Count = 14, First ENAME in the row set = KING
20 FORD Row Count = 14, First ENAME in the row set = KING
20 SCOTT Row Count = 14, First ENAME in the row set = KING
30 ALLEN Row Count = 14, First ENAME in the row set = KING
30 WARD Row Count = 14, First ENAME in the row set = KING
30 MARTIN Row Count = 14, First ENAME in the row set = KING
30 BLAKE Row Count = 14, First ENAME in the row set = KING
30 TURNER Row Count = 14, First ENAME in the row set = KING
30 JAMES Row Count = 14, First ENAME in the row set = KING
The query above tells us FETCH_ROWS() is also only called once when executing TABLE_SEMANTICS() with no PARTITION BY clause.
select deptno, ename, introspection from ptf_demo.table_semantics( scott.emp PARTITION BY DEPTNO ) order by deptno, ename ;
DEPTNO ENAME INTROSPECTION
---------- ---------- ------------------------------------------------------------
10 CLARK Row Count = 3, First ENAME in the row set = CLARK
10 KING Row Count = 3, First ENAME in the row set = CLARK
10 MILLER Row Count = 3, First ENAME in the row set = CLARK
20 ADAMS Row Count = 5, First ENAME in the row set = ADAMS
20 FORD Row Count = 5, First ENAME in the row set = ADAMS
20 JONES Row Count = 5, First ENAME in the row set = ADAMS
20 SCOTT Row Count = 5, First ENAME in the row set = ADAMS
20 SMITH Row Count = 5, First ENAME in the row set = ADAMS
30 ALLEN Row Count = 6, First ENAME in the row set = ALLEN
30 BLAKE Row Count = 6, First ENAME in the row set = ALLEN
30 JAMES Row Count = 6, First ENAME in the row set = ALLEN
30 MARTIN Row Count = 6, First ENAME in the row set = ALLEN
30 TURNER Row Count = 6, First ENAME in the row set = ALLEN
30 WARD Row Count = 6, First ENAME in the row set = ALLEN
The third query reveals that adding a PARTITION BY clause causes FETCH_ROWS() to be called once per distinct partition key value.
ORDER BY
As with analytic functions the ORDER BY clause in the PTF table argument controls the input row set order. The SELECT command's ORDER BY clause controls the output row set order. The following example demonstrates both clauses.
select deptno, ename, introspection from ptf_demo.table_semantics( scott.emp partition by deptno ORDER BY ENAME DESC ) ORDER BY DEPTNO, ENAME ASC ;
DEPTNO ENAME INTROSPECTION
---------- ---------- ------------------------------------------------------------
10 CLARK Row Count = 3, First ENAME in the row set = MILLER
10 KING Row Count = 3, First ENAME in the row set = MILLER
10 MILLER Row Count = 3, First ENAME in the row set = MILLER
20 ADAMS Row Count = 5, First ENAME in the row set = SMITH
20 FORD Row Count = 5, First ENAME in the row set = SMITH
20 JONES Row Count = 5, First ENAME in the row set = SMITH
20 SCOTT Row Count = 5, First ENAME in the row set = SMITH
20 SMITH Row Count = 5, First ENAME in the row set = SMITH
30 ALLEN Row Count = 6, First ENAME in the row set = WARD
30 BLAKE Row Count = 6, First ENAME in the row set = WARD
30 JAMES Row Count = 6, First ENAME in the row set = WARD
30 MARTIN Row Count = 6, First ENAME in the row set = WARD
30 TURNER Row Count = 6, First ENAME in the row set = WARD
30 WARD Row Count = 6, First ENAME in the row set = WARD
Replication
The fact that FETCH_ROWS() executes once per distinct partition key value means we can set the replication factor to a different value for each partition, as demonstrated next.
create or replace package body ptf_demo as
function describe(
p_input_table_metadata in out dbms_tf.table_t )
return
dbms_tf.describe_t
as
v_new_column_desc dbms_tf.describe_t ;
begin
--
-- make DEPTNO available to get_row_set()
--
p_input_table_metadata.column(8).for_read := true ;
--
-- create a new output column
--
v_new_column_desc.new_columns(1).name := 'INTROSPECTION' ;
v_new_column_desc.new_columns(1).type := dbms_tf.type_varchar2 ;
v_new_column_desc.row_replication := true ;
return v_new_column_desc ;
end describe ;
procedure fetch_rows as
v_deptno dbms_tf.tab_number_t ;
v_introspection dbms_tf.tab_varchar2_t ;
v_input_row_number pls_integer ;
v_output_row_number pls_integer := 1 ;
v_replication_factor naturaln := 1 ;
begin
dbms_tf.get_col( 1, v_deptno );
--
-- let's replicate once for each DEPTNO 10 row,
-- twice for each DEPTNO 20 row, and three times for
-- each DEPTNO 30 row
--
v_replication_factor := v_deptno(1) / 10 ;
--
-- assemble the output rows
--
v_input_row_number := v_deptno.first ;
while v_input_row_number is not null loop
for v_copy_number in 1 .. v_replication_factor loop
v_introspection(v_output_row_number) :=
'Replication factor = ' || v_replication_factor ;
v_output_row_number := v_output_row_number + 1 ;
end loop ;
v_input_row_number := v_deptno.next(v_input_row_number) ;
end loop ;
dbms_tf.row_replication( v_replication_factor ) ;
dbms_tf.put_col( 1, v_introspection );
end fetch_rows ;
end ptf_demo ;
/
select deptno, ename, introspection
from ptf_demo.table_semantics( scott.emp partition by deptno order by ename )
order by deptno, ename ;
DEPTNO ENAME INTROSPECTION
---------- ---------- ------------------------------------------------------------
10 CLARK Replication factor = 1
10 KING Replication factor = 1
10 MILLER Replication factor = 1
20 ADAMS Replication factor = 2
20 ADAMS Replication factor = 2
20 FORD Replication factor = 2
20 FORD Replication factor = 2
20 JONES Replication factor = 2
20 JONES Replication factor = 2
20 SCOTT Replication factor = 2
20 SCOTT Replication factor = 2
20 SMITH Replication factor = 2
20 SMITH Replication factor = 2
30 ALLEN Replication factor = 3
30 ALLEN Replication factor = 3
30 ALLEN Replication factor = 3
30 BLAKE Replication factor = 3
30 BLAKE Replication factor = 3
30 BLAKE Replication factor = 3
30 JAMES Replication factor = 3
30 JAMES Replication factor = 3
30 JAMES Replication factor = 3
30 MARTIN Replication factor = 3
30 MARTIN Replication factor = 3
30 MARTIN Replication factor = 3
30 TURNER Replication factor = 3
30 TURNER Replication factor = 3
30 TURNER Replication factor = 3
30 WARD Replication factor = 3
30 WARD Replication factor = 3
30 WARD Replication factor = 3
Beware of bug 28165108. Code that encounters a replication factor of 0 may produce incorrect output until this bug is fixed.