crear tablas temporales en procedimientos oracle

formas de evitar las tablas temporales globales en el oráculo

Question

Simplemente convertimos nuestros procedimientos almacenados de servidor SQL a procedimientos de Oracle. Los SP de Servidor Sql eran altamente dependientes de las tablas de sesión ( INSERT INTO #table1... ) estas tablas se convirtieron como tablas temporales globales en Oracle. Terminamos con aproximadamente 500 GTT para nuestros 400 SP

Ahora descubrimos que trabajar con GTT en Oracle se considera una última opción debido al rendimiento y otros problemas.

¿Qué otras alternativas hay? Colecciones? Cursores?

Nuestro uso típico de GTT es así:

Insertar en GTT

INSERT INTO some_gtt_1
  (column_a,
   column_b,
   column_c)
  (SELECT someA,
      someB,
      someC
     FROM TABLE_A
    WHERE condition_1 = ''YN756''
      AND type_cd = ''P''
      AND TO_NUMBER(TO_CHAR(m_date, ''MM'')) = ''12''
      AND (lname LIKE (v_LnameUpper || ''%'') OR
      lname LIKE (v_searchLnameLower || ''%''))
      AND (e_flag = ''Y'' OR
      it_flag = ''Y'' OR
      fit_flag = ''Y''));

Actualiza el GTT

UPDATE some_gtt_1 a
SET column_a = (SELECT b.data_a FROM some_table_b b 
               WHERE a.column_b = b.data_b AND a.column_c = ''C'')
WHERE column_a IS NULL OR column_a = '' '';

y luego obtener los datos del GTT. Estas son solo consultas de muestra, en realidad las consultas son realmente completas con muchas combinaciones y subconsultas.

Tengo una pregunta de tres partes:

1. ¿Puede alguien mostrar cómo transformar las consultas de muestra anteriores a colecciones y / o cursores?
2. Dado que con GTT puedes trabajar de forma nativa con SQL ... ¿por qué te alejas de los GTT? ¿realmente son tan malos?
3. ¿Cuáles deberían ser las pautas sobre cuándo usar y cuándo evitar GTT?

Answer 1

Primero respondamos la segunda pregunta:

"¿Por qué alejarse de los GTT? ¿Realmente son tan malos?"

Hace un par de días estaba sacando una prueba de concepto que cargó un archivo XML grande (~ 18MB) en un XMLType. Como no quería almacenar el XMLType permanentemente, traté de cargarlo en una variable PL / SQL (memoria de sesión) y una tabla temporal. Cargarlo en una tabla temporal tomó cinco veces más tiempo que cargarlo en una variable XMLType (5 segundos en comparación con 1 segundo). La diferencia se debe a que las tablas temporales no son estructuras de memoria: se escriben en el disco (específicamente el espacio de tabla temporal designado).

Si desea almacenar en caché una gran cantidad de datos, almacenarlos en la memoria acentuará la PGA, lo cual no es bueno si tiene muchas sesiones. Entonces, es una compensación entre RAM y tiempo.

A la primera pregunta

"¿Puede alguien mostrar cómo transformar las consultas de muestra anteriores a colecciones y / o cursores?"

Las consultas que publica se pueden combinar en una sola declaración:

SELECT case when a.column_a IS NULL OR a.column_a = '' '' 
           then b.data_a
           else  column_a end AS someA,
       a.someB,
       a.someC
FROM TABLE_A a
      left outer join TABLE_B b
          on ( a.column_b = b.data_b AND a.column_c = ''C'' )
WHERE condition_1 = ''YN756''
  AND type_cd = ''P''
  AND TO_NUMBER(TO_CHAR(m_date, ''MM'')) = ''12''
  AND (lname LIKE (v_LnameUpper || ''%'') OR
  lname LIKE (v_searchLnameLower || ''%''))
  AND (e_flag = ''Y'' OR
  it_flag = ''Y'' OR
  fit_flag = ''Y''));

(Simplemente transpuse su lógica, pero esa case() podría reemplazarse por una nvl2(trim(a.column_a), a.column_a, b.data_a) más nvl2(trim(a.column_a), a.column_a, b.data_a) ).

Sé que dices que tus consultas son más complicadas, pero tu primer puerto de escala debería ser volver a escribirlas. Sé lo seductor que es dividir una consulta engorrosa en muchos SQL para bebés combinados con PL / SQL, pero SQL puro es mucho más eficiente.

Para usar una colección, es mejor definir los tipos en SQL, ya que nos da la flexibilidad de usarlos en sentencias de SQL así como en PL / SQL.

create or replace type tab_a_row as object
    (col_a number
     , col_b varchar2(23)
     , col_c date);
/
create or replace type tab_a_nt as table of tab_a_row;
/

Aquí hay una función de muestra, que devuelve un conjunto de resultados:

create or replace function get_table_a 
      (p_arg in number) 
      return sys_refcursor 
is 
    tab_a_recs tab_a_nt; 
    rv sys_refcursor; 
begin 
    select tab_a_row(col_a, col_b, col_c)  
    bulk collect into tab_a_recs 
    from table_a 
    where col_a = p_arg; 

    for i in tab_a_recs.first()..tab_a_recs.last() 
    loop 
        if tab_a_recs(i).col_b is null 
        then 
            tab_a_recs(i).col_b :=  ''something''; 
        end if; 
    end loop;  

    open rv for select * from table(tab_a_recs); 
    return rv; 
end; 
/ 

Y aquí está en acción:

SQL> select * from table_a
  2  /

     COL_A COL_B                   COL_C
---------- ----------------------- ---------
         1 whatever                13-JUN-10
         1                         12-JUN-10

SQL> var rc refcursor
SQL> exec :rc := get_table_a(1)

PL/SQL procedure successfully completed.

SQL> print rc

     COL_A COL_B                   COL_C
---------- ----------------------- ---------
         1 whatever                13-JUN-10
         1 something               12-JUN-10

SQL>

En la función, es necesario crear una instancia del tipo con las columnas, para evitar la excepción ORA-00947. Esto no es necesario cuando se completa un tipo de tabla PL / SQL:

SQL> create or replace procedure pop_table_a
  2        (p_arg in number)
  3  is
  4      type table_a_nt is table of table_a%rowtype;
  5      tab_a_recs table_a_nt;
  6  begin
  7      select *
  8      bulk collect into tab_a_recs
  9      from table_a
 10      where col_a = p_arg;
 11  end;
 12  /

Procedure created.

SQL> 

Finalmente, las pautas

"¿Cuáles deberían ser las pautas sobre cuándo usar y cuándo evitar GTT?"

Las tablas temporales globales son muy buenas cuando necesitamos compartir datos en caché entre diferentes unidades de programa en la misma sesión. Por ejemplo, si tenemos una estructura de informe genérica generada por una función única que se alimenta de un GTT que está poblado por uno de varios procedimientos. (Aunque incluso eso también podría implementarse con ref cursores dinámicos ...)

Las tablas temporales globales también son buenas si tenemos un montón de procesamiento intermedio que es demasiado complicado para ser resuelto con una sola consulta SQL. Especialmente si ese procesamiento debe aplicarse a subconjuntos de las filas recuperadas.

Pero, en general, la presunción debería ser que no necesitamos usar una tabla temporal. Asi que

1. Hazlo en SQL a menos que sea demasiado difícil, en tal caso ...
2. ... Hágalo en variables PL / SQL (generalmente colecciones) a menos que tome demasiada memoria, en tal caso ...
3. ... Hazlo con una tabla temporal global

Answer 2

En general, utilizaría una colección PL / SQL para almacenar pequeños volúmenes de datos (tal vez mil filas). Si los volúmenes de datos fueran mucho más grandes, usaría un GTT para que no sobrecarguen la memoria de proceso.

Así que podría seleccionar unos cientos de filas de la base de datos en una colección PL / SQL, luego recorrerlos para hacer algunos cálculos / eliminar algunos o lo que sea, luego insertar esa colección en otra tabla.

Si tuviera que lidiar con cientos de miles de filas, trataría de aplicar la mayor parte del procesamiento de ''levantamiento pesado'' en grandes sentencias de SQL. Eso puede o no requerir GTT.

Puede usar objetos de colección de nivel SQL como algo que se traduce con bastante facilidad entre SQL y PL / SQL

create type typ_car is object (make varchar2(10), model varchar2(20), year number(4));
/

create type typ_coll_car is table of typ_car;
/

select * from table (typ_coll_car(typ_car(''a'',''b'',1999), typ_car(''A'',''Z'',2000)));
MAKE       MODEL                           YEAR
---------- -------------------- ---------------
a          b                           1,999.00
A          Z                           2,000.00

declare
  v_car1 typ_car := typ_car(''a'',''b'',1999);
  v_car2 typ_car := typ_car(''A'',''Z'',2000);
  t_car  typ_coll_car := typ_coll_car();
begin
  t_car := typ_coll_car(v_car1, v_car2);
  FOR i in (SELECT * from table(t_car)) LOOP
    dbms_output.put_line(i.year);
    END LOOP;
end;
/