Category: Table Functions

The next step to performing CONNECT BY queries in a Polymorphic Table Function is to add the explicit fetch. First add this to the DESCRIBE function to ensure all columns are fetched.

FOR counter IN 1..p_tbl.column.count LOOP
  p_tbl.column(counter).for_read := TRUE;
END LOOP;

Then add a explicit FETCH to the FETCH_ROWS procedure.

DBMS_TF.GET_ROW_SET( rowset => v_rowset,
                     row_count => v_rowcount,
                     col_count => v_colcount);

And add a code stub to interrogate the rows as they are fetched.

-- for every get column...
FOR col_counter IN 1..v_colcount LOOP

  -- if the column is SDATE or EDATE then...
  IF v_rowset(col_counter).description.name IN ('"EDATE"','"SDATE"') THEN

   -- for every row in the row set...
   FOR row_counter IN 1..v_rowcount LOOP

     -- get the date value
     v_dt := v_rowset(col_counter).tab_date(row_counter);

   END LOOP; -- every row

  END IF; -- column is SDATE

END LOOP; -- every column

I’m wary of how well this whole effort will perform so I’m going to run another performance test similar to the one in the previous post. Here are the results.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.07       0.07          0        201          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch    20004      0.77       2.92          0      99786          0      300000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20010      0.85       3.00          0      99987          0      300000

Some interesting points when comparing this run to the previous test without the explicit fetch.

• CPU went down from 2.33 to 0.77
• Elapsed went up from 2.81 to 3.00
• Queried went down from 201-0-99786 to 200-0-99785

What caused the reduction in CPU?

I’ll dig into that in the next few articles in this series.

Another question I was asked at KSCOPE 2019 was if Polymorphic Table Functions (PTF) could mimic an SQL CONNECT BY query. The question borders between a real world usage and a home work assignment but I’ll give the benefit of the doubt and turn it into the next series of posts on performance.

Here is the SQL CONNECT BY query.

SELECT partno p,
       sdate,
       edate,
       level  l,
       SYS_CONNECT_BY_PATH(sdate, '/') path
  FROM prices
 START WITH TRUNC(sdate) = TRUNC(sysdate)
 CONNECT BY PRIOR edate  = sdate AND
   PRIOR partno = partno;

P SDATE     EDATE     L PATH
- --------- --------- - ------------------------------
1 02-AUG-19 12-AUG-19 1 /02-AUG-19
1 12-AUG-19 22-AUG-19 2 /02-AUG-19/12-AUG-19
1 22-AUG-19 01-SEP-19 3 /02-AUG-19/12-AUG-19/22-AUG-19
2 02-AUG-19 12-AUG-19 1 /02-AUG-19
2 12-AUG-19 22-AUG-19 2 /02-AUG-19/12-AUG-19

It works through a table of part prices to assemble a price history per part.

And here is the beginnings of a PTF to mimic it.

CREATE OR REPLACE PACKAGE ptf_pkg AS
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
           RETURN DBMS_TF.DESCRIBE_T;
END;

CREATE OR REPLACE PACKAGE BODY ptf_pkg AS
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
           RETURN DBMS_TF.DESCRIBE_T IS 
  BEGIN
    RETURN NULL;
  END;
END;

CREATE OR REPLACE FUNCTION ptf_func(p_tbl IN TABLE )
  RETURN TABLE PIPELINED
  TABLE POLYMORPHIC USING ptf_pkg;

I have specified TABLE POLYMORPHIC so that I can group the queried data.

And here is the PTF query partitioning and ordering the results automatically.

SELECT *
  FROM ptf_func(prices PARTITION BY partno
ORDER BY sdate );

PARTNO SDATE     EDATE     PRICE
------ --------- --------- -----
     1 02-AUG-19 12-AUG-19   100
     1 12-AUG-19 22-AUG-19   200
     1 22-AUG-19 01-SEP-19   500
     2 02-AUG-19 12-AUG-19    33
     2 12-AUG-19 22-AUG-19    31

Only the directly queried columns match the CONNECT BY ones at this point. Before I go any further I’ll compare performance because I don’t want to waste time if its slower right out of the gate.

I’ll generate test data with this PL/SQL.

DECLARE
  v_sdate DATE;
  v_edate DATE;
BEGIN
  -- for every freaking part...
  FOR part IN 1..<part> LOOP
    v_sdate := SYSDATE;
    v_edate := SYSDATE + 3;
    -- for every row of prices I want to add...
    FOR counter IN 1..<price> LOOP
      INSERT INTO prices
      VALUES(<part>,v_sdate,v_edate,100);
      v_sdate := v_edate;
      v_edate := v_sdate + 3;
    END LOOP;  -- every price row
  END LOOP;  -- every freaking part
END;

I’ll use 1,000 parts with 100 prices each for my test runs.

Here are the results from SQL Trace for 3 executions of the CONNECT BY query.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0         72          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch    20004      4.70       5.08          0     134229          0      300000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20010      4.71       5.09          0     134301          0      300000

Here are the results for 3 executions of the PTF.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.03       0.02          0        200          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch    20004      2.33       2.78          0      99785          0      300000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20010      2.36       2.81          0      99985          0      300000

The most interesting numbers are the CPU and elapsed time.

• CPU is 2.36 for the PTF vs 4.71 for the CONNECT BY (50% faster)
• ELA is 2.81 for the PTF vs vs 5.09 for the CONNECT BY (45% faster)

Will these results hold as I add the actual CONNECT BY behaviour? We’ll find out as I work through it in the next few posts.

Thanks for reading!

At the recent ODTUG KSCOPE conference I used this script to demonstrate 3 concepts:

1. Records fetched by a Polymorphic Table Function (PTF) can be removed from the output by specifying a replication factor of zero
2. Values for added columns can be replicated across returned rows
3. Great care is required to coordinate the replication of fetched rows and added columns

The output of the script was this:

YEAR    CITY    COL1 COL2
---- ---------- ---- ----
2019 Seattle    B      10
2019 Seattle    C      20
2019 Seattle    D      30
2017 San Antonio

The PTF fetched 3 rows; then replicated one of them 3 times (Seattle), removed one of them (Orlando) and replicated one of them (San Antonio) once for a total of four output rows. But the rows of added columns (COL1 and COL2) were not replicated enough so the final row had NULL values for them.

A thoughtful attendee asked if the opposite (more added rows than fetched rows could ever occur). The answer is no but I did not have time to demonstrate it so I’m doing that with this post.

I made the following change to add 100 rows to the added columns no matter what.

FOR counter IN 1..100 LOOP
  -- add a numeric value for NEW_COL1 in the row
  v_n_t.tab_number(counter) := 10 * counter;

And then after the loop I added this:

DBMS_OUTPUT.PUT_LINE('Count = ' || v_n_t.tab_number.COUNT);

Now the output is this:

YEAR       CITY COL1 COL2
---- ---------- ---- ----
2019 Seattle    B      10
2019 Seattle    C      20
2019 Seattle    D      30
2017 San Antonio

Count = 100

The number of rows returned was not influenced by extra rows of added columns.

Thanks for reading!