implestrat.com

These are the scripts I used for my Polymorphic Table Function presentation at the ODTUG KSCOPE 2019 conference.

Setup And Intro
Row Replication
Column Addition
Row Replication And Column Addition
GET_ROW_SET
Row Set Details
Get Value To Populate alues
Columns Parameter
CSTORE and XSTORE
Table Polymorphic and Partitioning
Partitioning Demo

Be sure to run the Setup and Intro scripts first.

Thanks for reading!

This post expands on TABLE POLYMORPHIC functionality by demonstrating how records are distributed across partitions. Two key points:

1. The maximum number of rows per rowset is 1024
2. Within the FETCH_ROWS procedure you can process the whole fetched data set at one time if desired.

First some demo data – 250 records each for COL1 values of 1 through 5.

CREATE TABLE table1
( col1 NUMBER );

BEGIN
  FOR x IN 1..5 LOOP
    FOR y IN 1..250 LOOP
      INSERT INTO table1
      VALUES(x);
    END LOOP;
  END LOOP;
END;

Here’s what the data looks like grouped by COL1.

SELECT col1,
       COUNT(*)
  FROM table1
GROUP BY col1
ORDER BY col1;

COL1 COUNT(*)
---- --------
   1      250
   2      250
   3      250
   4      250
   5      250
5 rows selected.

And the package header.

CREATE OR REPLACE PACKAGE poly_ptf AS

  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
    RETURN DBMS_TF.DESCRIBE_T;
  PROCEDURE open;
  PROCEDURE fetch_rows;
  PROCEDURE close;

END;

And the package body.

CREATE OR REPLACE PACKAGE BODY poly_ptf AS

  /*----------------------------------------------*/
  PROCEDURE display ( p VARCHAR2 ) IS
  /*----------------------------------------------*/
    v_x NUMBER;
  BEGIN
    DBMS_OUTPUT.PUT_LINE(p);
    IF NOT DBMS_TF.XSTORE_EXISTS('X',
           DBMS_TF.XSTORE_TYPE_NUMBER) THEN
      DBMS_TF.XSTORE_SET('X',1);
    END IF;
    DBMS_TF.XSTORE_GET('X',v_x);
    DBMS_TF.XSTORE_SET('X',v_x + 1);
    DBMS_TF.TRACE(v_x,TRUE);
  END;

  /*----------------------------------------------*/
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
  /*----------------------------------------------*/
    RETURN DBMS_TF.DESCRIBE_T IS
  BEGIN
    FOR counter IN 1..p_tbl.column.count LOOP
      p_tbl.column(counter).for_read := TRUE;
    END LOOP;
    RETURN NULL;
  END;

  /*----------------------------------------------*/
  PROCEDURE open IS
  /*----------------------------------------------*/
   v_x NUMBER;
  BEGIN
   display('OPEN');
  END;

  /*----------------------------------------------*/
  PROCEDURE fetch_rows IS
  /*----------------------------------------------*/
    v_rowset DBMS_TF.ROW_SET_T;
    v_ntab DBMS_TF.TAB_NUMBER_T;
  BEGIN

    DBMS_TF.GET_ROW_SET(v_rowset);
    display('FETCH_ROWS');

    v_ntab := v_rowset(1).tab_number;

    -- Here is where I process the whole fetched
    -- data set at one time by querying its
    -- associated values in tab_number
    FOR x IN ( SELECT column_value cv,
                      COUNT(*) c
                 FROM TABLE(v_ntab)
               GROUP BY column_value ) LOOP

      DBMS_OUTPUT.PUT_LINE('COL1=' || x.cv || ' : ' || x.c);

    END LOOP;

  END;

  /*----------------------------------------------*/
  PROCEDURE close IS
  /*----------------------------------------------*/
  BEGIN
    display('CLOSE');
  END;

END;

And the function.

CREATE OR REPLACE FUNCTION poly_func(p_tbl IN TABLE )
  RETURN TABLE PIPELINED
  TABLE POLYMORPHIC USING poly_ptf;

And the query:

SELECT *
FROM poly_func(table1 PARTITION BY col1 ORDER BY col1 );

And the results (I’ve removed the display of 1250 rows to save space and  added blank lines to show the separation better in the DBMS_OUTPUT).

OPEN
1 <id=6804000000000000>
FETCH_ROWS
2 <id=6804000000000000>
COL1=1 : 250
CLOSE
3 <id=6804000000000000>

OPEN
1 <id=6904000000000000>
FETCH_ROWS
2 <id=6904000000000000>
COL1=2 : 250
CLOSE
3 <id=6904000000000000>

OPEN
1 <id=6A04000000000000>
FETCH_ROWS
2 <id=6A04000000000000>
COL1=3 : 250
CLOSE
3 <id=6A04000000000000>

OPEN
1 <id=6B04000000000000>
FETCH_ROWS
2 <id=6B04000000000000>
COL1=4 : 250
CLOSE
3 <id=6B04000000000000>

OPEN
1 <id=6C04000000000000>
FETCH_ROWS
2 <id=6C04000000000000>
COL1=5 : 24
FETCH_ROWS
3 <id=6C04000000000000>
COL1=5 : 226
CLOSE
4 <id=6C04000000000000>

The interesting things is the last group used two FETCH_ROWS calls. This is because the magic 1024 records was hit as follows:

COL1 = 1 250 rows so total is 250
COL1 = 2 250 rows so total is 500
COL1 = 3 250 rows so total is 750
COL1 = 4 250 rows so total is 1000
COL1 = 5  24 rows so total is 1024

Then the last FETCH_ROWS was

COL1 = 5 226 rows so total is 1250

Thus I have to break my statement about 1024 into two parts:

1. For ROW POLYMORPHISM it is that maximum number of rows in a rowset
2. For TABLE POLYMORPHISM it is the number of cumulative rows at which another FETCH_ROWS is done

I cant help but think there is more to the story. I’ll continue to dig in future posts.

Thanks for reading!