自動實現 CTE？（而不是臨時表 CREATE 和 INSERT）

我是一個新手，想了解 Oracle 中的查詢優化和臨時表。

我看到的 Oracle 臨時表範例涉及 CREATE TABLE 和 INSERT INTO 語句。

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');

就我而言（IBM Maximo），我的查詢是純 SELECT 語句（視圖等），因此我無法處理其他語句，如 CREATE TABLE 或 INSERT INTO。

但是我遇到了一篇關於 SQL 性能的部落格，它描述了 PostgreSQL 中一些聽起來很有用的相關功能：

PostgreSQL 12+ 自動實現（將輸出分配到記憶體中）多次呼叫的 CTE。

對我未經訓練的眼睛來說，自動物化的想法似乎很有吸引力。它可以幫助我避免多次執行 CTE（不必要地）。

有沒有辦法在 Oracle 中做這種事情？（避免需要 CREATE TABLE 和 INSERT INTO 語句）

子句中的MATERIALIZE提示就是這樣做的。WITH

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 |
-------------------------------------------------------------------------------

另一種方法是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      |      |
---------------------------------

在上面的範例中，即使沒有INLINE提示，數據庫也會選擇後者。

引用自：https://dba.stackexchange.com/questions/299988