In the first three parts of the series we have seen how a PTF basically works, but we have focused on row-semantic PTF only. In this part we’ll look at the table semantic PTF. As a reminder, the main difference to row-semantic PTF is that it is not enough to look at the currently processed row to produce the result. With table semantic PTF we often also need some summarized state from the previously processed rows. They are useful to implement user defined aggregate or window functions. Let’s first try to implement a very basic example of table semantic PTF and learn more theory as we go. <\/p>\n
What will be our task? In the second part<\/a> we have previously developed a PTF to concatenate fields into a CSV-list. Let’s take this new column and summarize its length. Again, it’s very basic and doesn’t actually require any PTF, it can be simply done with SQL. We are doing it this way just to show how table semantic PTF works. <\/p>\n Listing 1: Package specification<\/em><\/p>\n There are no differences in package specification to what we’ve seen so far. We’ll be using the parameter cols2sum<\/strong><\/em> to pass the column(s) whose length we want to summarize. The definition of the PTF itself is where we tell the database about the semantic of the function. It is the word TABLE<\/strong> before the word POLYMORPHIC<\/strong>.<\/p>\n DESCRIBE<\/strong><\/p>\n After three previous parts of this series we now have some experience as PTF developer and can guess how the implementation of DESCRIBE could look like (see Listing 2). But it is important to note that we are making some sort of a shortcut here. Our implementation of DESCRIBE assumes the list of columns can be passed with parameter cols2sum<\/strong>, if we wanted to aggregate multiple columns at once. That means, we cannot just return one hard coded new column. We have to define them in a loop, because we just don’t know in advance how many there will be. But this shortcut is just okay for now. I will show the more generic case in one of the next posts. <\/p>\n Listing 2: Implementation of the DESCRIBE method<\/em><\/p>\n FETCH_ROWS<\/strong><\/p>\n What is to consider while implementing FETCH_ROWS? As already mentioned, you have no control over how often FETCH_ROWS is executed. The processing is done in chunks; one chunk is not larger than 1024 rows (not documented, but what I’ve seen so far). It makes sense because all the collections are kept in the PGA.<\/p>\n So if we have to aggregate something, we can only build a subtotal based on our actual chunk and then we need a mechanism to pass these subtotals from one execution to the next. There is a simple XSTORE<\/strong> interface for storing key-value pairs during the execution which we can use for our example. We are using the variable v_len<\/strong> to get and set the current sum (line 10 and 16) at the beginning and the end of the current execution of FETCH_ROWS.<\/strong> <\/p>\n Another possibility to handle the execution state using your own structures and the special execution ID returned by DBMS_TF.GET_XID<\/strong> function will be considered in later posts.<\/p>\n Listing 3: Implementation of the FETCH_ROWS method<\/em><\/p>\n We are ready to call the function (see Listing 4). The first attempt runs against a documented restriction: it is not allowed to nest polymorphic functions. But it is very easy to workaround this using a WITH clause.<\/p>\n Listing 4: Calling the PTF <\/em><\/p>\n Partitioning and Ordering <\/strong><\/p>\n With table semantic PTF the incoming data can be optionally partitioned and\/or ordered like in Listing 5. Note that this is not allowed for row semantic PTF. There are few limitations, however. You cannot simply use comma separated column lists, but need to enclose them in parenthesis. And it seems not to be possible to use any SQL expressions as in corresponding clauses of analytical functions, just simple column names. <\/p>\n Listing 5: Partitioning and ordering the incoming data<\/em><\/p>\n Note that if you partition the input, the FETCH_ROWS will be executed at least once per partition. In other words, you are always dealing with rows from one partition inside the FETCH_ROWS. But does this mean you don’t even need to know, whether or how the input was partitioned on PTF invocation? For the simplest case like our example this seems to be true. But in general I’m rather inconvenient with the fact that, inside a PTF, you don’t know what columns where referenced in a PARTITION BY or ORDER BY clause. For me, this information would be very helpful in some use cases. <\/p>\n Parallel execution<\/strong><\/p>\n We haven’t looked at the parallel execution yet. Of course, SQL queries containing a polymorphic table function can be executed in parallel. For row semantic PTF the database can distribute the rows between the parallel slaves without any restrictions. On the other hand, if data is partitioned, as can be the case with table semantic functions, all rows of the same partition have to be processed by the same parallel slave. It is due to the execution states that are stored and used by consequent executions of FETCH_ROWS: we don’t want the different partition-specific states are interchanged with each other. <\/p>\n This also means, if we don’t partition the input of the table semantic PTF, we will actually be having a serialization point in the query.<\/p>\n PASS_THROUGH columns<\/strong><\/p>\n Do PASS_THROUGH columns behave something different with table semantic PTF? According to the documentation they should. That’s what we can read:<\/p>\n \nAll columns in the Row Semantics PTF are marked as pass-through by default. For Table Semantics PTF, the default value for pass-through is set to false. For the Table Semantics PTF, the partitioning columns are always pass-through and this cannot be changed by the DESCRIBE function.\n<\/p><\/blockquote>\n But actually I couldn’t find any differences. I don’t know, whether it is a documentation or implementation bug, but it simply doesn’t work as described for me. First, we have already seen in Listing 2 that all columns were passed through, although we didn’t set this explicitly. Let’s modify the function DESCRIBE so that we set PASS_THROUGH to FALSE for all columns :<\/p>\n Listing 6: Partitioning and ordering the incoming data<\/em><\/p>\n So we could set all columns including the partitioning column DEPTNO to be no PASS_THROUGH. Doesn’t make very much sense for me.<\/p>\n Aggregate functions<\/strong><\/p>\n The PTF from our example behaves like analytical or window function. But could we build the real aggregate function? The one, that returns only one value (row) per table\/partition? Let’s try to emulate the simple function SUM() as an example. The idea is to use the row replication to remove all rows but one, which will be used to output the aggregation result.<\/p>\n Listing 7: EMULATE_SUM aggregate function<\/em><\/p>\n Seems to work, doesn’t it? It does, but only as long as we have very small data sets so that FETCH_ROWS will be executed only once. The problem is, you should eliminate all rows and just do the desired aggregation for all rowsets, except for the last one. But you don’t know whether the active rowset is the last one. So we are doing that thing for all rowsets. <\/p>\n Look what happens using a data set containing more than 1024 rows (Listing 8):<\/p>\n Listing 8: EMULATE_SUM on a bigger data set<\/em><\/p>\n In fact we are returning the sum once per rowset, but only the last result is correct and is actually needed. For me it is a significant limitation of the table semantic PTF at the moment.<\/p>\n What’s next<\/strong>\r\n\r\nCREATE OR REPLACE PACKAGE sumlen_package AS\r\n\r\nFUNCTION describe ( tab IN OUT dbms_tf.table_t\r\n , cols2sum IN dbms_tf.columns_t ) \r\n RETURN dbms_tf.describe_t ;\r\n \r\nPROCEDURE fetch_rows;\r\n\r\nEND sumlen_package; \r\n\/\r\n\r\nCREATE OR REPLACE FUNCTION sumlen_ptf (tab TABLE, cols2sum COLUMNS )\r\n RETURN TABLE PIPELINED TABLE POLYMORPHIC USING sumlen_package;\r\n\/\r\n\r\n<\/pre>\n
\r\n\r\nCREATE OR REPLACE PACKAGE BODY sumlen_package AS\r\n\r\nFUNCTION describe (tab IN OUT dbms_tf.table_t\r\n , cols2sum IN dbms_tf.columns_t ) \r\n RETURN dbms_tf.describe_t IS\r\nBEGIN\r\n FOR I IN 1 .. tab.COLUMN.COUNT LOOP \r\n IF tab.COLUMN(i).description.name MEMBER OF cols2sum THEN\r\n tab.column(i).for_read := true;\r\n END IF;\r\n END LOOP;\r\n \r\n RETURN dbms_tf.describe_t( \r\n new_columns => \r\n dbms_tf.columns_new_t( \r\n 1 => dbms_tf.column_metadata_t(\r\n name => 'SUM_LEN', \r\n TYPE => dbms_tf.type_number)));\r\n \r\nEND; \r\n...\r\n\r\n<\/pre>\n
\r\n\r\nPROCEDURE fetch_rows IS\r\n rowset dbms_tf.row_set_t; \r\n colcnt PLS_INTEGER;\r\n rowcnt PLS_INTEGER;\r\n len_curr_col dbms_tf.tab_number_t; \r\n v_len pls_integer;\r\n v_currlen pls_integer := 0;\r\nBEGIN \r\n dbms_tf.get_row_set(rowset, rowcnt, colcnt);\r\n dbms_tf.xstore_get('len', v_len);\r\n v_currlen := nvl(v_len, 0);\r\n FOR r IN 1..rowcnt LOOP\r\n v_currlen := v_currlen + length (rowset(1).tab_varchar2(r));\r\n len_curr_col(r) := v_currlen ;\r\n END LOOP;\r\n dbms_tf.xstore_set('len', v_len+v_currlen);\r\n dbms_tf.put_col(1, len_curr_col); \r\nEND fetch_rows;\r\n\r\nEND sumlen_package; \r\n\/\r\n\r\n<\/pre>\n\r\n\r\nSQL> -- Nesting PTF doesn't work\r\nSELECT * \r\nFROM sumlen_ptf(my_ptf(scott.emp, COLUMNS(deptno))\r\n , COLUMNS(agg_col))\r\n\r\nORA-62569: nested polymorphic table function is disallowed\r\n\r\nSQL> \r\nSQL> -- But it works via CTE (WITH clause)\r\nSQL> WITH agg as (SELECT * FROM my_ptf(scott.emp, COLUMNS(deptno)))\r\n 2 SELECT * \r\n 3 FROM sumlen_ptf(agg , COLUMNS(agg_col));\r\n\r\n DEPTNO AGG_COL ... SUM_LEN\r\n---------- -----------------------------... ----------\r\n 20 7369;"SMITH";"CLERK";7902;"17... 52\r\n 30 7499;"ALLEN";"SALESMAN";7698;... 111\r\n 30 7521;"WARD";"SALESMAN";7698;"... 169\r\n 20 7566;"JONES";"MANAGER";7839;"... 224\r\n 30 7654;"MARTIN";"SALESMAN";7698... 285\r\n 30 7698;"BLAKE";"MANAGER";7839;"... 340\r\n 10 7782;"CLARK";"MANAGER";7839;"... 395\r\n 20 7788;"SCOTT";"ANALYST";7566;"... 450\r\n 10 7839;"KING";"PRESIDENT";;"17.... 502\r\n 30 7844;"TURNER";"SALESMAN";7698... 560\r\n 20 7876;"ADAMS";"CLERK";7788;"23... 613\r\n 30 7900;"JAMES";"CLERK";7698;"03... 665\r\n 20 7902;"FORD";"ANALYST";7566;"0... 719\r\n 10 7934;"MILLER";"CLERK";7782;"2... 773\r\n\r\n14 rows selected. \r\n\r\n<\/pre>\n
\r\n\r\nSQL> SELECT deptno, empno, ename, sum_len \r\n 2 FROM sumlen_ptf (scott.emp PARTITION BY deptno \r\n 3 ORDER BY empno\r\n 4 , COLUMNS(ename));\r\n\r\n DEPTNO EMPNO ENAME SUM_LEN\r\n---------- ---------- ---------- ----------\r\n 10 7782 CLARK 5\r\n 10 7839 KING 9\r\n 10 7934 MILLER 15\r\n 20 7369 SMITH 5\r\n 20 7566 JONES 10\r\n 20 7788 SCOTT 15\r\n 20 7876 ADAMS 20\r\n 20 7902 FORD 24\r\n 30 7499 ALLEN 5\r\n 30 7521 WARD 9\r\n 30 7654 MARTIN 15\r\n 30 7698 BLAKE 20\r\n 30 7844 TURNER 26\r\n 30 7900 JAMES 31\r\n\r\n14 rows selected. \r\n\r\nSQL> \r\nSQL> -- You need parenthesis for column lists in partition by or order by\r\nSQL> \r\nSQL> SELECT deptno, empno, ename, sum_len \r\n 2 FROM sumlen_ptf (scott.emp PARTITION BY deptno, mgr\r\n 3 ORDER BY empno\r\n 4 , COLUMNS(ename));\r\n\r\nORA-62555: Invalid use of PARTITION BY or ORDER BY clause.\r\n\r\nSQL> \r\nSQL> -- but this works\r\nSQL> SELECT deptno, mgr, empno, ename, sum_len \r\n 2 FROM sumlen_ptf (scott.emp PARTITION BY (deptno, mgr)\r\n 3 ORDER BY empno\r\n 4 , COLUMNS(ename));\r\n\r\n DEPTNO MGR EMPNO ENAME SUM_LEN\r\n---------- ---------- ---------- ---------- ----------\r\n 10 7782 7934 MILLER 6\r\n 10 7839 7782 CLARK 5\r\n 10 7839 KING 4\r\n 20 7566 7788 SCOTT 5\r\n 20 7566 7902 FORD 9\r\n 20 7788 7876 ADAMS 5\r\n 20 7839 7566 JONES 5\r\n 20 7902 7369 SMITH 5\r\n 30 7698 7499 ALLEN 5\r\n 30 7698 7521 WARD 9\r\n 30 7698 7654 MARTIN 15\r\n 30 7698 7844 TURNER 21\r\n 30 7698 7900 JAMES 26\r\n 30 7839 7698 BLAKE 5\r\n\r\n14 rows selected. \r\n\r\nSQL> \r\nSQL> -- No expressions possible \r\nSQL> \r\nSQL> SELECT deptno, empno, ename, sum_len \r\n 2 FROM sumlen_ptf (scott.emp PARTITION BY SUBSTR(deptno,1,1)\r\n 3 ORDER BY empno\r\n 4 , COLUMNS(ename));\r\n\r\nORA-00907: missing right parenthesis\r\n\r\n<\/pre>\n
\r\n\r\nFUNCTION describe (tab IN OUT dbms_tf.table_t\r\n , cols2sum IN dbms_tf.columns_t ) \r\n RETURN dbms_tf.describe_t IS\r\nBEGIN\r\n FOR I IN 1 .. tab.COLUMN.COUNT LOOP \r\n IF tab.COLUMN(i).description.name MEMBER OF cols2sum THEN\r\n tab.column(i).for_read := true;\r\n END IF;\r\n tab.column(i).pass_through := false;\r\n END LOOP;\r\n \r\n RETURN dbms_tf.describe_t( \r\n new_columns => \r\n dbms_tf.columns_new_t( \r\n 1 => dbms_tf.column_metadata_t(\r\n name => 'SUM_LEN', \r\n TYPE => dbms_tf.type_number)));\r\nEND; \r\n\r\n...\r\n\r\nSQL> SELECT *\r\n 2 FROM sumlen_ptf (scott.emp PARTITION BY deptno \r\n 3 ORDER BY empno\r\n 4 , COLUMNS(ename));\r\n\r\n SUM_LEN\r\n----------\r\n 5\r\n 9\r\n 15\r\n 5\r\n 10\r\n 15\r\n 20\r\n 24\r\n 5\r\n 9\r\n 15\r\n 20\r\n 26\r\n 31\r\n\r\n14 rows selected. \r\n\r\n<\/pre>\n
\r\n\r\n-- What we want to emulate\r\nSQL> select sum(sal) from scott.emp;\r\n\r\n SUM(SAL)\r\n----------\r\n 29025\r\n\r\nCREATE OR REPLACE PACKAGE emulate_sum_pkg AS \r\n \r\nFUNCTION describe(tab IN OUT DBMS_TF.TABLE_T, \r\n agg_col IN DBMS_TF.COLUMNS_T) \r\nRETURN DBMS_TF.DESCRIBE_T; \r\n \r\nPROCEDURE fetch_rows; \r\n \r\nEND emulate_sum_pkg; \r\n\/ \r\nPackage created.\r\n\r\nCREATE OR REPLACE PACKAGE BODY emulate_sum_pkg AS \r\nFUNCTION describe(tab IN OUT DBMS_TF.TABLE_T, \r\n agg_col IN DBMS_TF.COLUMNS_T) \r\nRETURN DBMS_TF.DESCRIBE_T \r\nAS \r\nBEGIN \r\n-- No pass-through columns \r\n FOR I IN 1 .. tab.COLUMN.COUNT LOOP \r\n tab.column(i).pass_through := false; \r\n IF tab.COLUMN(i).description.name MEMBER OF agg_col THEN \r\n TAB.COLUMN(i).for_read := TRUE; \r\n END IF; \r\n END LOOP; \r\n RETURN DBMS_TF.DESCRIBE_T(new_columns => \r\n dbms_tf.columns_new_t( 1 => \r\n dbms_tf.column_metadata_t(name => 'SUM_COL' , \r\n TYPE => dbms_tf.type_number)) \r\n , row_replication=>true); \r\nEND; \r\n \r\nPROCEDURE fetch_rows IS \r\n inp_rs DBMS_TF.row_set_t; \r\n env dbms_tf.env_t := dbms_tf.get_env(); \r\n colcnt PLS_INTEGER; \r\n rowcnt PLS_INTEGER; \r\n repfac dbms_tf.tab_naturaln_t; \r\n sumcol dbms_tf.tab_number_t; \r\n sumcurr number; \r\nBEGIN \r\n DBMS_TF.get_row_set(inp_rs, rowcnt, colcnt); \r\n IF NOT dbms_tf.xstore_exists('sumcurr') THEN \r\n dbms_tf.xstore_set('sumcurr',0); \r\n sumcurr := 0; \r\n ELSE \r\n dbms_tf.xstore_get('sumcurr', sumcurr); \r\n END IF; \r\n \r\n FOR i IN 1 .. rowcnt LOOP \r\n -- Actually we should only leave one row in the last rowset, \r\n -- after having summarized ALL data \r\n -- Not knowing whether the row set is the last one, \r\n -- we have to do this for every rowset \r\n \r\n -- Eliminate all rows except the first \r\n IF i = 1 THEN \r\n repfac(i) := 1; \r\n ELSE \r\n repfac(i) := 0; \r\n END IF; \r\n \r\n sumcurr := sumcurr + inp_rs(1).tab_number(i); \r\n END LOOP; \r\n dbms_tf.xstore_set('sumcurr',sumcurr); \r\n inp_rs(1).tab_number(1) := sumcurr; \r\n dbms_tf.row_replication(replication_factor => repfac); \r\n dbms_tf.trace('Rows in rowset: '||env.row_count); \r\n dbms_tf.put_row_set(inp_rs, repfac); \r\nEND; \r\n \r\nEND emulate_sum_pkg; \r\n\/\r\nPackage Body created.\r\n\r\nCREATE OR REPLACE FUNCTION emulate_sum (tab TABLE, agg_col COLUMNS ) \r\n RETURN TABLE PIPELINED table POLYMORPHIC USING emulate_sum_pkg; \r\n\/\r\nFunction created.\r\n\r\nSQL> SELECT * \r\n 2 FROM emulate_sum(scott.emp, COLUMNS(sal));\r\n\r\n SUM_COL\r\n----------\r\n 29025\r\n\r\n<\/pre>\n\r\n\r\nSQL> WITH emp_big AS (SELECT * \r\n 2 FROM scott.emp \r\n 3 CROSS JOIN \r\n 4 (SELECT 1 \r\n 5 FROM dual \r\n 6 CONNECT BY level <=100)) \r\n 7 SELECT * \r\n 8 FROM emulate_sum(emp_big, COLUMNS(sal));\r\n\r\n\r\n SUM_COL\r\n----------\r\n 2121225\r\n 2902500\r\n\r\nRows in rowset: 1024\r\nRows in rowset: 376\r\n\r\n<\/pre>\n
\nThis was the last post in 2018. Of course, next year I will continue to investigate the polymorphic table functions. Stay tuned! Hoping that PTF will become more mature in 2019!
\n
\nMerry Christmas and Happy New Year 2019! <\/strong><\/em><\/p>\nRelated Posts<\/h3>