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.