主页 / dba / 问题 / 299988
Accepted
User1974
Asked: 2021-09-23 08:58:44 +0800 CST

自动实现 CTE？（而不是临时表 CREATE 和 INSERT）

  • 772

我是一个新手，想了解 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 语句）

oracle performance

1 个回答

  1. Best Answer

    子句中的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提示，数据库也会选择后者。

    • 1

相关问题