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

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