ways to avoid global temp tables in oracle

Question

We just converted our sql server stored procedures to oracle procedures. Sql Server SP's were highly dependent on session tables (INSERT INTO #table1...) these tables got converted as global temporary tables in oracle. We ended up with aroun 500 GTT's for our 400 SP's

Now we are finding out that working with GTT's in oracle is considered a last option because of performance and other issues.

what other alternatives are there? Collections? Cursors?

Our typical use of GTT's is like so:

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

Update the 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 = ' ';

and later on get the data out of the GTT. These are just sample queries, in actuality the queries are really complext with lot of joins and subqueries.

I have a three part question:

1. Can someone show how to transform the above sample queries to collections and/or cursors?
2. Since with GTT's you can work natively with SQL...why go away from the GTTs? are they really that bad.
3. What should be the guidelines on When to use and When to avoid GTT's

Answer 1

Generally I'd use a PL/SQL collection for storing small volumes of data (maybe a thousand rows). If the data volumes were much larger, I'd use a GTT so that they don't overload the process memory.

So I might select a few hundred rows from the database into a PL/SQL collection, then loop through them to do some calculation/delete a few or whatever, then insert that collection into another table.

If I was dealing with hundreds of thousands of rows, I would try to push as much of the 'heavy lifting' processing into large SQL statements. That may or may not require GTT.

You can use SQL level collection objects as something that translates quite easily between SQL and 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;
/

Answer 2

Let's answer the second question first:

"why go away from the GTTs? are they really that bad."

A couple of days ago I was knocking up a proof of concept which loaded a largish XML file (~18MB) into an XMLType. Because I didn't want to store the XMLType permanently I tried loading it into a PL/SQL variable (session memory) and a temporary table. Loading it into a temporary table took five times as long as loading it into an XMLType variable (5 seconds compared to 1 second). The difference is because temporary tables are not memory structures: they are written to disk (specifically your nominated temporary tablespace).

If you want to cache a lot of data then storing it in memory will stress the PGA, which is not good if you have lots of sessions. So it's a trade-off between RAM and time.

To the first question:

"Can someone show how to transform the above sample queries to collections and/or cursors?"

The queries you post can be merged into a single statement:

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

(I have simply transposed your logic but that case() statement could be replaced with a neater nvl2(trim(a.column_a), a.column_a, b.data_a) ).

I know you say your queries are more complicated but your first port of call should be to consider rewriting them. I know how seductive it is to break a gnarly query into lots of baby SQLs stitched together with PL/SQL but pure SQL is way more efficient.

To use a collection it is best to define the types in SQL, because it gives us the flexibility to use them in SQL statements as well as 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;
/

Here's a sample function, which returns a result set:

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

And here it is in action:

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>

In the function it is necessary to instantiate the type with the columns, in order to avoid the ORA-00947 exception. This is not necessary when populating a PL/SQL table type:

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> 

Finally, guidelines

"What should be the guidelines on When to use and When to avoid GTT's"

Global temp tables are very good when we need share cached data between different program units in the same session. For instance if we have a generic report structure generated by a single function feeding off a GTT which is populated by one of several procedures. (Although even that could also be implemented with dynamic ref cursors ...)

Global temporary tables are also good if we have a lot of intermediate processing which is just too complicated to be solved with a single SQL query. Especially if that processing must be applied to subsets of the retrieved rows.

But in general the presumption should be that we don't need to use a temporary table. So

1. Do it in SQL unless it is too hard it which case ...
2. ... Do it in PL/SQL variables (usually collections) unless it takes too much memory it which case ...
3. ... Do it with a Global Temporary Table