PTF’S – Row Sets Deep Dive Part 1

The previous post used a PTF to populate row number one from a one row table. It also did this in row set number one from the first, and only execution of the FETCH_ROWS procedure. This post shows the simplest way to increase executions of FETCH_ROWS — increase the number of rows to process.

I’ll use this query to vaidate the changes. It shows the count of rows with each value in the ADDED_COL. Here’s the results using the code from the previous post.

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

ADDED_COL         COUNT(*)
--------------- ----------
Added Col                1

There is only 1 row so it shows with a count of 1.

Next I’ll add another row.

INSERT INTO table1
VALUES(2018);

And query the counts again.

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

ADDED_COL         COUNT(*)
--------------- ----------
Added Col                1
                         1

It shows 1 row (the very first one in the rowset) with an ADDED_COL value of “Added Col” and 1 row with a NULL value.

Next I’ll set the table up with 1025 rows.

DELETE table1;
BEGIN
  FOR counter IN 1..1025 LOOP
    INSERT INTO table1
    VALUES(counter);
  END LOOP;
END;

And run the count query again.

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

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

The results show 2 rows with an ADDED_COL value of “Added Col” – that must mean two row sets from two executions of the FETCH_ROWS procedure. I’ll augment the FETCH_ROWS procedure to verify it.

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;
  DBMS_TF.PUT_ROW_SET( rowset => v_rowset);
END;

And run the query again.

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

ADDED_COL         COUNT(*)
--------------- ----------
Added Col                2
                      1023
ROWCOUNT = 1024
ROWCOUNT = 1

Sure enough FETCH_ROWS was run twice. Why was 1025 the number of rows that caused this? At this point I don’t know. But stay tuned for more details of row sets.

Thanks for reading.