Execution State Management 1

This post begins the discussion of PTF Execution State Management. That’s a fancy way of describing the state of an executing PTF – specifically the OPEN, FETCH_ROWS and CLOSE procedures. And the state is not really managed – its simply recorded in KEY-VALUE (think JSON) pairs called XSTORE’s.

Here is a simple example in a FETCH_ROWS procedure that does the following:

  1. Saves an XSTORE numeric value of 0
  2. For every row fetched (3 in this case)
    1. Assigns the XSTORE numeric value to a column in the row set
    2. Increments the XSTORE numeric value
PROCEDURE fetch_rows IS

  v_row_set    DBMS_TF.ROW_SET_T;
  v_xstore_col DBMS_TF.COLUMN_DATA_T;
  v_env        DBMS_TF.ENV_T;
  v_xstore     NUMBER;

BEGIN

  -- if nothing exists for key ABC then
  -- add a numeric value of zero
  IF NOT DBMS_TF.XSTORE_EXISTS('ABC') THEN
    DBMS_TF.XSTORE_SET('ABC',0);
  END IF;

  v_env := DBMS_TF.GET_ENV;

  -- for every row...
  FOR counter IN 1..v_env.row_count LOOP

    -- get the value for the ABC numeric key and add it
    -- to the rowset
    DBMS_TF.XSTORE_GET('ABC',v_xstore);
    v_xstore_col.tab_varchar2(counter) := v_xstore;

    -- increment the ABC numeric value by 1
    DBMS_TF.XSTORE_SET('ABC',v_xstore + 1);

  END LOOP; -- every row

  -- kram the local rowset to the main one
  v_row_set(1) := v_xstore_col;
  DBMS_TF.PUT_ROW_SET(v_row_set);

END;

And here is the output.

SELECT *
  FROM poly_func(table1);
XSTORE
---------------
0
1
2

The next few posts will delve into XSTORE values more.

Thanks for reading.

PTF’S – Row Sets Deep Dive Part 3

The previous 2 posts demonstrated options for row replication at the PTF or row set level. This post demonstrates the third option – setting replication at the row level.

First up is some demo data.

CREATE TABLE table1
( col1 NUMBER NOT NULL PRIMARY KEY );

INSERT INTO table1
VALUES(2018);

INSERT INTO table1
VALUES(2019);

INSERT INTO table1
VALUES(2020);

Most of code (package, function, etc) for this post is borrowed from the prior post except for the FETCH_ROWS procedure. Here’s the first version of it introducing the TAB_NATURALN_T datatype to store replication values for each row in the data set. In this case we set the replication as follows:

  • 1 for the first row (2018)
  • 2 for the second row (2019)
  • 0 for the third row (2020)
PROCEDURE fetch_rows IS
  v_row_set DBMS_TF.ROW_SET_T;
  v_rep_tab DBMS_TF.TAB_NATURALN_T;
BEGIN
  v_rep_tab(1) := 1;
  v_rep_tab(2) := 2;
  v_rep_tab(3) := 0;
  DBMS_TF.GET_ROW_SET( rowset => v_row_set );
  DBMS_TF.PUT_ROW_SET( rowset => v_row_set,
                       replication_factor => v_rep_tab );
END;

And here are the results.

SELECT *
  FROM poly_func(table1);
COL1
----
2018
2019
2019
3 rows selected.

2018 replicated one time, 2019 replicated two times and 2020 replicated zero times.

Next up is:

  • 1 for the first row (2018)
  • 2 for the second row (2019)
  • no value for the third row (2020)
PROCEDURE fetch_rows IS
  v_row_set DBMS_TF.ROW_SET_T;
  v_rep_tab DBMS_TF.TAB_NATURALN_T;
BEGIN
  v_rep_tab(1) := 1;
  v_rep_tab(2) := 2;
  DBMS_TF.GET_ROW_SET( rowset => v_row_set );
  DBMS_TF.PUT_ROW_SET( rowset => v_row_set,
                       replication_factor => v_rep_tab );
END;

And the results:

SELECT *
  FROM poly_func(table1);
COL1
----
2018
2019
2019
2020
4 rows selected.

2018 replicated ones time, 2019 replicated two times and 2020 replicated one time.

And here is where things get interesting:

  • 1 for the first row (2018)
  •  no value for the second row (2019)
  • 3 for the third row (2020)
PROCEDURE fetch_rows IS
  v_row_set DBMS_TF.ROW_SET_T;
  v_rep_tab DBMS_TF.TAB_NATURALN_T;
BEGIN
  v_rep_tab(1) := 1;
  v_rep_tab(3) := 3;
  DBMS_TF.GET_ROW_SET( rowset => v_row_set );
  DBMS_TF.PUT_ROW_SET( rowset => v_row_set,
                       replication_factor => v_rep_tab );
END;

And the surprising results…

SELECT *
  FROM poly_func(table1);
COL1
----
2018
2019
2020
3 rows selected.

It appears that a gap in the entries in the natural table causes the replication factor to be ignored altogether producing one of each row. I’m not sure if that is a bug or the intended behaviour.

Thanks for reading!

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