Posted on by wordpressimplestrat

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!