Início / dba / Perguntas / 299988
Accepted
User1974
Asked: 2021-09-23 08:58:44 +0800 CST

Materializar CTE automaticamente? (em vez da tabela temporária CREATE e INSERT)

  • 772

Eu sou um novato tentando aprender sobre otimização de consultas e tabelas temporárias no Oracle.

Os exemplos que vi para tabelas temporárias do Oracle envolvem instruções CREATE TABLE e 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');

No meu caso (IBM Maximo), minhas consultas são instruções SELECT puras (visualizações, etc.), então não posso incluir outras instruções como CREATE TABLE ou INSERT INTO.

Mas me deparei com um blog sobre SQL Performance que descreve algumas funcionalidades relacionadas no PostgreSQL que parecem úteis:

O PostgreSQL 12+ automaticamente materializa (aloca a saída na memória) CTEs que são chamadas mais de uma vez.

Para meus olhos destreinados, a ideia de materialização automática parece atraente. Isso poderia me ajudar a evitar executar CTEs várias vezes (desnecessariamente).

Existe uma maneira de fazer esse tipo de coisa no Oracle? (evitando a necessidade de instruções CREATE TABLE e INSERT INTO)

oracle performance

1 respostas

  1. Best Answer

    A MATERIALIZEdica na WITHcláusula faz isso.

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

    A outra maneira é 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      |      |
---------------------------------

    No exemplo acima, o banco de dados escolhe o último mesmo sem INLINEdica.

    • 1

relate perguntas