Способы избежать глобальных временных таблиц в oracle

Ответ 1

Сначала ответьте на второй вопрос:

"Зачем уходить от GTT? действительно, что плохо."

Несколько дней назад я выбивал доказательство концепции, которое загружало довольно большой XML файл (~ 18 МБ) в XMLType. Поскольку я не хотел постоянно хранить XMLType, я попробовал загрузить его в переменную PL/SQL (память сеанса) и временную таблицу. Загрузка его во временную таблицу занимает в пять раз больше времени, чем загрузка его в переменную XMLType (5 секунд по сравнению с 1 секундой). Разница заключается в том, что временные таблицы не являются структурами памяти: они записываются на диск (в частности, ваше назначенное временное табличное пространство).

Если вы хотите кэшировать большое количество данных, тогда их сохранение в памяти будет подчеркивать PGA, что не очень хорошо, если у вас много сеансов. Итак, это компромисс между ОЗУ и временем.

К первому вопросу:

"Может кто-нибудь показать, как преобразовать над примерами запросов к коллекциям и/или курсоры?"

Запросы, которые вы публикуете, могут быть объединены в один оператор:

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

(Я просто транспонировал вашу логику, но оператор case() мог быть заменен более аккуратным nvl2(trim(a.column_a), a.column_a, b.data_a)).

Я знаю, что вы говорите, что ваши запросы сложнее, но ваш первый порт захода должен состоять в том, чтобы переписать их. Я знаю, как соблазнительно разбить грубый запрос на множество детских SQL, сшитых вместе с PL/SQL, но чистый SQL является более эффективным.

Чтобы использовать коллекцию, лучше всего определить типы в SQL, потому что она дает нам возможность использовать их в операторах SQL, а также 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;
/

Здесь примерная функция, возвращающая набор результатов:

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

И здесь он находится в действии:

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>

В функции необходимо создать экземпляр типа с столбцами, чтобы избежать исключения ORA-00947. Это не обязательно при заполнении типа таблицы 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> 

Наконец, рекомендации

"Какими должны быть руководящие принципы, касающиеся Когда использовать и когда избегать GTT's

Глобальные таблицы temp очень хороши, когда нам нужно делиться кэшированными данными между различными программными модулями в одном сеансе. Например, если у нас есть общая структура отчета, генерируемая одной функцией, подающей GTT, которая заполняется одной из нескольких процедур. (Хотя даже это может быть реализовано с помощью динамических курсоров ref...)

Глобальные временные таблицы также хороши, если у нас есть много промежуточной обработки, которая слишком сложна для решения одним SQL-запросом. Особенно, если эта обработка должна применяться к подмножествам найденных строк.

Но в целом презумпция должна заключаться в том, что нам не нужно использовать временную таблицу. Так

• Сделайте это в SQL, если это не слишком сложно, в каком случае...
• ... Делайте это в PL/SQL-переменных (обычно в коллекциях), если не требуется слишком много памяти, в каком случае...
• ... Сделайте это с помощью глобальной временной таблицы

Ответ 2

Как правило, я бы использовал коллекцию PL/SQL для хранения небольших томов данных (может быть, тысячи строк). Если объемы данных были намного больше, я бы использовал GTT, чтобы они не перегружали память процесса.

Поэтому я мог бы выбрать несколько сотен строк из базы данных в коллекцию PL/SQL, затем прокрутить их, чтобы выполнить некоторые вычисления/удалить несколько или что-то еще, а затем вставить эту коллекцию в другую таблицу.

Если бы я имел дело со сотнями тысяч строк, я бы постарался подтолкнуть большую часть "тяжелой" обработки к большим SQL-операторам. Это может требовать или не требовать GTT.

Вы можете использовать объекты коллекции уровня SQL как нечто, что легко переносится между SQL и 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;
/