Are PTF Added Columns Visible – Part 2

In a previous post I showed how columns added by a PTF can be used by the outside world (or a simple Oracle view masquerading as the outside world). This post discusses how added columns are seen, or not seen, by the inside world of the Oracle optimizer.

-- a demo table
CREATE TABLE table1
( col1 NUMBER NOT NULL PRIMARY KEY );

-- with demo data
INSERT INTO table1
VALUES(2019);

-- and a PTF package and body
CREATE OR REPLACE PACKAGE poly_pkg AS
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
  RETURN DBMS_TF.DESCRIBE_T;
  PROCEDURE fetch_rows;
END;

CREATE OR REPLACE PACKAGE BODY poly_pkg AS
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
  RETURN DBMS_TF.DESCRIBE_T AS
    v_new_col1 DBMS_TF.COLUMN_METADATA_T;
    v_new_cols DBMS_TF.COLUMNS_NEW_T;
  BEGIN
    -- add a varchars(15) column named ADDED_COL
    -- to the result set of this PTF
    v_new_col1 := DBMS_TF.COLUMN_METADATA_T( 
                   type => DBMS_TF.TYPE_VARCHAR2,
                   name => 'ADDED_COL',
                   max_len => 15 );
     v_new_cols := DBMS_TF.COLUMNS_NEW_T( 1 => v_new_col1 );
     RETURN DBMS_TF.DESCRIBE_T ( new_columns => v_new_cols );
  END;

  PROCEDURE fetch_rows IS
    v_rowset DBMS_TF.ROW_SET_T;
    v_added DBMS_TF.COLUMN_DATA_T;
  BEGIN
    -- populate the new column with hard
    -- coded text
    v_added.tab_varchar2(1) := 'Added Col';
    v_rowset(1) := v_added;
    DBMS_TF.PUT_ROW_SET( rowset => v_rowset);
  END;

END;

-- last but not least the PTF
CREATE OR REPLACE FUNCTION poly_func(p_tbl IN TABLE )
  RETURN TABLE PIPELINED
  ROW POLYMORPHIC USING poly_pkg;

Now a query using the added column as criteria.

SELECT *
  FROM poly_func(table1)
 WHERE added_col = 'Added Col';

COL1 ADDED_COL
---------- ---------------
2019 Added Col

What did the optimizer do with that query? Lets look at the explain plan:

Plan hash value: 1059882907
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 2 (0)| 00:00:01 |
|* 1 | POLYMORPHIC TABLE FUNCTION | POLY_FUNC | 1 | 13 | | |
| 2 | INDEX FAST FULL SCAN | SYS_C008053 | 1 | 13 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ADDED_COL"='Added Col')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)

In simplest terms it shows a select was executed against the PTF and an index was used to find the required rows. A filter was then applied using the value from the WHERE clause.

Wait – was the index on the added column? Lets check…

SELECT table_name,
       column_name
  FROM user_ind_columns
 WHERE index_name = 'SYS_C008053;

TABLE_NAME           COLUMN_NAME
-------------------- --------------------
TABLE1               COL1

The index is on the tables primary key – not the added column. This makes sense because the overhead to maintain an index for every single dynamically added column would be onerous.

In future articles I’ll investigate way that may help out the optimizer when working with PTF’s.

Thans for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *

+ 12 = 16