0

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)

3
  • Well, you would only create a table once, so I don't see how that is a problem. Yes, oracle does implement CTE, but as you describe, it is purely an in-memory structure, so would not be of a lot of use in learning about query optimization. Why would you not want to do your learning with normal, permanent tables?
    – EdStevens
    Sep 22 at 17:11
  • @EdStevens Thanks for your reply. Unfortunately, don’t have any db privileges in my IBM Maximo environments, other than SELECT. I’m really only able to mock up queries in Toad and then create views through the application UI. So I’m looking for optimization techniques that I can use in my pure SELECT queries. Cheers.
    – User1973
    Sep 22 at 17:25
  • "the idea of automatic materialization seems appealing" - why? It may or may not be beneficial from the performance point of view, so why not leave this decision to the query optimizer? Do you have reasons to believe it's making wrong choices?
    – mustaccio
    Sep 22 at 20:01
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.