Return to 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)

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)

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 subqueries or 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)

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)

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 UI), my queries are pure SELECT statements (views, etc.) where I can’t tack on other statements like CREATE TABLE or INSERT INTO. But when reading a blog about SQL Performance,, I came across a blurb that describes something that PostgreSQL does:

For instance, 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.

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

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 subqueries or 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)