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:
Global Temporary Tables (GTTs) are the Oracle tables, having data type as private; such that data inserted by a session can be accessed by that session only. The session-specific rows in a GTT can be preserved for the entire session, as AI report tables are created using ON COMMIT PRESERVE ROWS clause.
In Oracle there isn't any difference. When you create a temporary table in an Oracle database, it is automatically global, and you are required to include the "Global" key word. The SQL standard, which defines how the term "GLOBAL TEMPORARY TABLE" is interpreted, allows for either a LOCAL or GLOBAL scope.
We can also use the following query to display all Oracle global temporary tables: select table_name from all_tables where temporary = 'Y';
"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.
"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>
"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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With