Oracle 12c Expand SQL Text

The new PL/SQL procedure DBMS_UTILITY.expand_sql_text can be used to generate a single SQL statement, referencing only tables, from a query involving one or more views. On the surface of it, this sounded great for getting a single view (in the non Oracle sense) of the tables involved in a query. The signature of DBMS_UTILITY.expand_sql_text is

PROCEDURE EXPAND_SQL_TEXT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 INPUT_SQL_TEXT                 CLOB                    IN
 OUTPUT_SQL_TEXT                CLOB                    OUT
    

Although a single query is produced by DBMS_UTILITY.expand_sql_text, it may take a bit of reformatting and manual simplification to get your head round. By way of example, consider the simple query below involving three tables, and one view.

Example table and view set up script

CREATE TABLE vt1 (
  id            NUMBER NOT NULL,
  payload       VARCHAR2(30) NOT NULL,
  CONSTRAINT vt1_pk PRIMARY KEY (id)
);

CREATE TABLE vt2 (
  id            NUMBER NOT NULL,
  payload       VARCHAR2(30) NOT NULL,
  CONSTRAINT vt2_pk PRIMARY KEY (id)
);

CREATE TABLE vt3 (
  id            NUMBER NOT NULL,
  payload       VARCHAR2(30) NOT NULL,
  CONSTRAINT vt3_pk PRIMARY KEY (id)
);

CREATE VIEW vt1_and_vt2_join
AS
  SELECT id, a.payload a_payload, b.payload b_payload
  FROM   vt1 a
  JOIN   vt2 b
  USING  (id)
    

The join of interest

SELECT *
FROM   vt3
JOIN   vt1_and_vt2_join
USING  (id);
    

Using DBMS_UTILITY.expand_sql_text to obtain the query "with the identical meaning" to the above join which references only tables

DECLARE
  lc_query_in   CLOB;
  lc_query_out  CLOB;
BEGIN
  lc_query_in := 'SELECT * FROM vt3 JOIN vt1_and_vt2_join USING (id)';

  DBMS_UTILITY.expand_sql_text(lc_query_in, lc_query_out);

  DBMS_OUTPUT.put_line(lc_query_out);
END;
/
    

The raw output produced by DBMS_UTILITY.expand_sql_text for our example is

SELECT "A1"."ID_0" "ID","A1"."PAYLOAD_1" "PAYLOAD","A1"."A_PAYLOAD_2" "A_PAYLOAD","A1"."B_PAYLOAD_3" "B_PAYLOAD" FROM (SELECT "A2"."ID" "ID_0","A3"."PAYLOAD" "PAYLOAD_1","A2"."A_PAYLOAD" "A_PAYLOAD_2","A2"."B_PAYLOAD" "B_PAYLOAD_3" FROM "RIK"."VT3" "A3", (SELECT "A4"."ID_0" "ID","A4"."QCSJ_C000000000600000_1" "A_PAYLOAD","A4"."QCSJ_C000000000600001_2" "B_PAYLOAD" FROM (SELECT "A5"."ID" "ID_0","A6"."PAYLOAD" "QCSJ_C000000000600000_1","A5"."PAYLOAD" "QCSJ_C000000000600001_2" FROM RIK."VT1" "A6",RIK."VT2" "A5" WHERE "A6"."ID"="A5"."ID") "A4") "A2" WHERE "A3"."ID"="A2"."ID") "A1"

As the above is a bit unreadable, removing the quotes, putting owner, table, field and alias names in lower case, and formatting the output a bit, yields the following query

SELECT a1.id_0        id,
       a1.payload_1   payload,
       a1.a_payload_2 a_payload,
       a1.b_payload_3 b_payload
FROM   (SELECT a2.id        id_0,
               a3.payload   payload_1,
	       a2.a_payload a_payload_2,
	       a2.b_payload b_payload_3
        FROM   rik.vt3 a3,
               (SELECT a4.id_0                    id,
                       a4.qcsj_c000000000600000_1 a_payload,
                       a4.qcsj_c000000000600001_2 b_payload
                FROM  (SELECT a5.id id_0,
                              a6.payload qcsj_c000000000600000_1,
                              a5.payload qcsj_c000000000600001_2
                       FROM   rik.vt1 a6,
                              rik.vt2 a5
                       WHERE  a6.id = a5.id
                      ) a4
               ) a2
        WHERE  a3.id = a2.id
       ) a1;
    

From the above one can see the DBMS_UTILITY.expand_sql_text approach to obtain an output query for a given input query is

1. Perform view join, and create a subquery from the result (Oracle will introduce some new names in this step)
2. Use the join subquery to generate a presentation subquery with result fields presented "as expected" (as named in the view in question)
3. Steps 1. and 2. are used many times, producing many subqueries, until all view texts are merged (in the simple example above, no further iteration was required)
4. Produce an input join subquery using the subqueries produced in steps 1-3
5. Produce a final presentation query to present the input join subquery (and all it's subqueries) with the field names aliased as in the input query

So each view in turn in the DBMS_UTILITY.expand_sql_text input query results in a "join" subquery, and a wrapping "presentation" subquery. Views joined in either the input query or in another view within the input query also result in both a "join" subquery and "presentation" subquery.

It strikes me that it wouldn't be that hard to merge each join and presentation subquery pair into a single subquery. Given that this has not been done, I'm sure there must be a good reason why not, but, for now at least, I can't think of it.