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

Давайте сначала ответим на второй вопрос:

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

Пару дней назад я делал пробный вариант, который загружал большой XML файл (~18MB) в 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"

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

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

Но в целом следует исходить из того, что нам не нужно использовать временную таблицу. Поэтому

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