Automatically materialize CTE? (instead of temporary table CREATE and INSERT)

Question

I’m a novice trying to learn about query optimization and temporary tables in Oracle.

The examples I’ve seen for Oracle temporary tables involve CREATE TABLE and INSERT INTO statements.

CREATE PRIVATE TEMPORARY TABLE ora$ppt_temp1(
    id INT,
    description VARCHAR2(100)
) ON COMMIT DROP DEFINITION;

INSERT INTO ora$ppt_temp1(id,description)
VALUES(1,'Transaction-specific private temp table');

In my case (IBM Maximo), my queries are pure SELECT statements (views, etc.), so I can’t tack on other statements like CREATE TABLE or INSERT INTO.

But I came across a blog about SQL Performance that describes some related functionality in PostgreSQL that sounds useful:

PostgreSQL 12+ automatically materializes (allocates the output into memory) CTEs, which are called more than once.

To my untrained eye, the idea of automatic materialization seems appealing. It could help me avoid running CTEs multiple times unnecessarily.

Is there a way to do that sort of thing in Oracle? (avoiding the need for CREATE TABLE and INSERT INTO statements)

Answer 1

The MATERIALIZE hint in the WITH clause does that.

SQL> with cte as (select /*+ materialize */ 1,'Transaction-specific private temp table' from dual)
  2  select * from cte union all select * from cte union all select * from cte;

         1 'TRANSACTION-SPECIFICPRIVATETEMPTABLE'
---------- ---------------------------------------
         1 Transaction-specific private temp table
         1 Transaction-specific private temp table
         1 Transaction-specific private temp table

SQL> select * from table(dbms_xplan.display_cursor(format=>'basic'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
with cte as (select /*+ materialize */ 1,'Transaction-specific private
temp table' from dual) select * from cte union all select * from cte
union all select * from cte

Plan hash value: 451459878

-------------------------------------------------------------------------------
| Id  | Operation                                | Name                       |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D66E6_1F24A80 |
|   3 |    FAST DUAL                             |                            |
|   4 |   UNION-ALL                              |                            |
|   5 |    VIEW                                  |                            |
|   6 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D66E6_1F24A80 |
|   7 |    VIEW                                  |                            |
|   8 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D66E6_1F24A80 |
|   9 |    VIEW                                  |                            |
|  10 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D66E6_1F24A80 |
-------------------------------------------------------------------------------

The other way is INLINE:

SQL> with cte as (select /*+ inline */ 1,'Transaction-specific private temp table' from dual)
  2  select * from cte union all select * from cte union all select * from cte;

         1 'TRANSACTION-SPECIFICPRIVATETEMPTABLE'
---------- ---------------------------------------
         1 Transaction-specific private temp table
         1 Transaction-specific private temp table
         1 Transaction-specific private temp table

SQL> select * from table(dbms_xplan.display_cursor(format=>'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
with cte as (select /*+ inline */ 1,'Transaction-specific private temp
table' from dual) select * from cte union all select * from cte union
all select * from cte

Plan hash value: 2623540522

---------------------------------
| Id  | Operation        | Name |
---------------------------------
|   0 | SELECT STATEMENT |      |
|   1 |  UNION-ALL       |      |
|   2 |   FAST DUAL      |      |
|   3 |   FAST DUAL      |      |
|   4 |   FAST DUAL      |      |
---------------------------------

In the above example, the database choses the later even without INLINE hint.