PTF’S – Row Sets Deep Dive Part 2

The previous post demonstrated multiple row sets being processed by a single PTF execution. This one expands on that by showing how each row set can have its own replication factor. Here is the new FETCH_ROWS procedure I’ll use:

PROCEDURE fetch_rows IS
  v_rowset DBMS_TF.ROW_SET_T;
  v_added DBMS_TF.COLUMN_DATA_T;
  v_env DBMS_TF.ENV_T;
BEGIN
  v_env := DBMS_TF.GET_ENV;
  DBMS_OUTPUT.PUT_LINE('ROWCOUNT = ' || v_env.row_count);
  v_added.tab_varchar2(1) := 'Added Col';
  v_rowset(1) := v_added;
  --
  -- Set the replication factor equal to the number of
  -- rows in the rowset
  --
  DBMS_TF.ROW_REPLICATION(replication_factor => 
                               v_env.row_count );
  DBMS_TF.PUT_ROW_SET( rowset => v_rowset);
END;

And here is the query and results:

SELECT added_col,
       COUNT(*)
  FROM poly_func(table1)
GROUP BY added_col;

ADDED_COL         COUNT(*)
--------------- ----------
Added Col                2
                   1048575

ROWCOUNT = 1024
ROWCOUNT = 1

The first rowset

  • was 1,024 rows
  • had a replication factor of 1,024
  • became 1,048,576 rows after applying the replication factor of 1.024
  • the ADDED_COL for the first row was “Added Col” while the rest remained null
  • contributed 1,048,575 rows to the NULL count
  • contributed 1 row to the “Added Col” count

The second rowset

  • was 1 row
  • had a replication factor of 1
  • became 1 row after applying the replication factor of 1
  • the ADDED_COL for the first, and only,  row was “Added Col”
  • contributed 1 row to the “Added Col” count

Thanks for reading