This post expands on TABLE POLYMORPHIC functionality by demonstrating how records are distributed across partitions. Two key points:
- The maximum number of rows per rowset is 1024
- 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:
- For ROW POLYMORPHISM it is that maximum number of rows in a rowset
- 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!