Describe Only PTF’s Don’t Have A Plan

The previous post in this series introduced Polymorphic Table Functions (PTF’s) in a very simplistic way. All they have is a DESCRIBE function. Thus they are referred to as “Describe Only PTF’s”.  Oracle does not include them in the query plan.

Below are all of the SQL statements from the previous post along with their output from the DBMS_UTILITY.EXPAND_SQL_TEXT procedure to display.

-- Query 1 column from the PTF
SELECT col1
  FROM poly_func(table1);

That query translates to this:

SELECT "A1"."COL1" "COL1"
  FROM (SELECT "A2"."COL1" "COL1",
               "A2"."COL2" "COL2"
          FROM (SELECT "A3"."COL1" "COL1",
                       "A3"."COL2" "COL2"
                  FROM "D"."TABLE1" "A3") "A2") "A1";

The next query fails to find a row.

SELECT col1
  FROM poly_func(table1)
 WHERE 1 = 2;

It translated to this SQL:

SELECT "A1"."COL1" "COL1"
  FROM (SELECT "A2"."COL1" "COL1",
               "A2"."COL2" "COL2"
          FROM (SELECT "A3"."COL1" "COL1",
                       "A3"."COL2" "COL2"
                  FROM "D"."TABLE1" "A3") "A2") "A1" 
  WHERE 1=2;

Querying a PTF with the view like this:

SELECT *
  FROM poly_func(view1);

Translates to this:

SELECT "A1"."COL2" "COL2"
  FROM (SELECT "A2"."COL2" "COL2"
          FROM (SELECT "A3"."COL2" "COL2"
                  FROM (SELECT "A4"."COL2" "COL2"
                          FROM "D"."TABLE1" "A4") "A3") "A2") "A1"

And finally the WITH cause:

WITH t1 AS
( SELECT col2 AS c2
    FROM table1 )
SELECT *
  FROM poly_func(t1);

Translates to:

SELECT "A1"."C2" "C2"
  FROM (SELECT "A3"."C2" "C2" 
          FROM (SELECT "A4"."C2" "C2"
                  FROM (SELECT "A2"."COL2" "C2"
                          FROM "D"."TABLE1" "A2") "A4") "A3") "A1";

The optimizer relies heavily on nested queries. The reasons for that will become clearer when we work through more complex examples in later posts.

Thanks for reading.