Table Polymorphism and 1024

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!

Table Polymorphism Execution IDs

The Oracle documentation states that an Execution ID is “…a unique execution id that can be used by the PTF to index any cursor execution specific runtime state.” The previous post in about Table Polymorphism demonstrated that each partition of data got its own OPEN, FETCH_ROWS and CLOSE procedure. Let’s see if each one get’s its own execution ID as well.

First up – three amazing rows of demo data!

CREATE TABLE table1
( col1 NUMBER );

BEGIN
  FOR x IN 1..3 LOOP
    INSERT INTO table1
    VALUES(x);
  END LOOP;
END;

And the PTF package.

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
    -- a private procedure to display values and 
    -- get/set an XSTORE value
    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);
    -- output the XSTORE value plus the Execution ID
    DBMS_TF.TRACE( msg => v_x,
                   with_id => TRUE);
  END;

  /*----------------------------------------------*/
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
  /*----------------------------------------------*/
    RETURN DBMS_TF.DESCRIBE_T IS
  BEGIN
    RETURN NULL;
  END; 

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

  /*----------------------------------------------*/
  PROCEDURE fetch_rows IS
  /*----------------------------------------------*/
    v_rowset DBMS_TF.ROW_SET_T;
  BEGIN
    display('FETCH_ROWS');
  END;

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

END;

And the PTF itself.

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

And a query with results.

SELECT *
  FROM poly_func(table1 PARTITION BY col1 );

COL1
----
   1
   2
   3
3 rows selected.

OPEN
1 <id=6504000000000000>
FETCH_ROWS
2 <id=6504000000000000>
CLOSE
3 <id=6504000000000000>
OPEN
1 <id=6604000000000000>
FETCH_ROWS
2 <id=6604000000000000>
CLOSE
3 <id=6604000000000000>
OPEN
1 <id=6704000000000000>
FETCH_ROWS
2 <id=6704000000000000>
CLOSE
3 <id=6704000000000000>

Each partition of COL1 values got its own execution ID (6504, 6604, 6704).

I’ll continue to expand on this in future posts.

Thanks!