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

[The examples I’ve seen][1] 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,][2] my queries are pure SELECT statements, in views, [etc][3]. where I can’t tack on other statements like CREATE TABLE or INSERT INTO. But when reading [a blog about SQL Performance,][4], 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)


  [1]: https://www.oracletutorial.com/oracle-basics/oracle-private-temporary-table/
  [2]: https://www.ibm.com/docs/en/mam/7.6.0?topic=doa-adding-views-databases
  [3]: https://desktop.arcgis.com/en/arcmap/latest/map/working-with-layers/what-is-a-query-layer-.htm
  [4]: https://towardsdatascience.com/sql-performance-tips-2-128a31e8ecb