Oracle Global Temporary Tables

Oracle Global Temporary Tables

Global temporary tables can be used to drastically improve query performance for complex queries, especially for the complex queries that have multiple summarizations and comparisons. Global temporary tables are used much like regular tables (heap table), but have some major differences that must be understood.

Think of a global temporary table as a “work table” and used more in reporting environments then in online systems as the performance gain from there use applies more to the complex queries generated to report back information. A lot of code I have seen is using permanent tables used by many session for “temporary work”. Overall performance can be improved using a global temporary table due to reduced redo logging with global temporary tables as well as reduce the maintenance overhead of removing rows and all user sessions using the same table and table segments. However global temporary tables is not a replacement for using PL/SQL tables, if using PL/SQL and the data size is not too large it is far better to use a PL/SQL table.

There may those that question the use of global temporary tables over SQL using the WITH clause, keep in mind that depending on the version of Oracle global temporary tables with an index might be the better option.

1. Global temporary tables do not use a permanent tablespace, but rather use the users assigned temporary tablespace therefore global temporary tables do not have a storage clause.
2. Global temporary tables do not allocate a segment when created segments are not allocated until a row is inserted into the global temporary table.
3. Global temporary tables do not preserve the data across a user's sessions or transactions, the data is temporary to a session or transaction depending on how the global temporary table is defined. When defined transaction wise the data is removed upon commit or rollback, on session wise the data is remove upon session end.
4. When a global temporary table is used by multiple users each user has their own temporary segment for storing data therefore all users can see the structure but user a can not see user b's data even though they may be using the same global temporary table.
5. Global temporary tables can have indexes created on them, but only when the global temporary table is empty.
6. Global temporary tables do not recognize logging/no/logging clause.
7. Truncate does work on a global temporary table, but only removes the data for the session executing the truncate.
8. Global temporary tables can not contain varray or nested table columns.
9. Global temporary tables do not support parallel query or parallel DML operations.
10. DML on global temporary tables do not generate redo for data changes, however it does record in the undo and that record is recorded in the redo. Therefore global temporary tables reduce redo, but doe not eliminate it. This makes redo generate at least ½ or possible less then ½ that of a permanent (heap) table.


Uses for Global Temporary Tables

1. when using PL/SQL and the data is too large for a PL/SQL table.
2. when not using PL/SQL and a PL/SQL table is not an option.
3. complex queries that do complex summarizations and comparisons.
4. some correlated sub-queries to improve performance.
5. in situations where data is needed for a transaction for a session and does not need to live longer then the session and the data does not need to be shared across sessions.



Create Global Temporary Tables

Transaction level global temporary table

create global temporary table temp_emp_trn
( empno number primary key,
deptno number,
ename varchar2(30)
on commit delete rows ;

Session level global temporary table

create global temporary table temp_emp_ses
( empno number primary key,
deptno number,
ename varchar2(30)
on commit preserve rows ;



Example using global temporary tables to improve performance/resource usage

Improve performance on nested sub-queries

An example may be we want to see if we have any employees that have never had a performance review. Employees can have many performance reviews over time therefore there could be many records in the review table for each employee so over time as the review table grows this could get to be a more intensive operation.

The query would look like this.

select empno, deptno, ename
from scott.emp
where empno NOT IN
(select empno from review) ;

Elapsed: 00:00:00.03

-----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-----------------------------------------------------------------------------
0 SELECT STATEMENT 108 7128 244 (1) 00:00:03
* 1 HASH JOIN ANTI NA 108 7128 244 (1) 00:00:03
2 TABLE ACCESS FULL EMP 2005 103K 5 (0) 00:00:01
3 TABLE ACCESS FULL REVIEW 41175 522K 239 (1) 00:00:03
-----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
52 recursive calls
0 db block gets
897 consistent gets
0 physical reads
0 redo size
659 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
5 rows processed


Let see what it would look like using global temporary tables where we can generate a list of distinct employee’s empno from the review table. This will reduce the read for each iteration of the not in.

create global temporary table temp_review
on commit delete rows
as
select empno
from review
where 1=0 ;

-- populate the global tempoary table
insert into temp_review
select distinct empno
from review ;

-- select using the global temporary table
select empno, deptno, ename
from emp
where
empno not in
(select empno from temp_review) ;

Elapsed: 00:00:00.01

-------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-------------------------------------------------------------------------------------
0 SELECT STATEMENT 5 330 9 (12) 00:00:01
* 1 HASH JOIN RIGHT ANTI NA 5 330 9 (12) 00:00:01
2 TABLE ACCESS FULL TEMP_REVIEW 2000 26000 3 (0) 00:00:01
3 TABLE ACCESS FULL EMP 2005 103K 5 (0) 00:00:01
-------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
659 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
5 rows processed


Now this has to be balanced out with the cost and time it takes to build the global temporary tables, but as you can se we can significantly reduce the work of a query by using a global temporary table. Now reduction in work does not always equal a reduction in elapse runtime, but a system under load should see performance improvement when amount of work is reduced. The example here over several executions either showed elapse time improvement or the elapse time was equal.



Complete Script using Global Temporary Tables

set linesize 300

-- spool our output to a file for review
spool global_temporary_tables.out

-- drop our 2 tables in case they aready exist
drop table emp ;
drop table review ;

-- create our 2 permenant tables
create table emp
( empno number,
deptno number,
ename varchar2(50)
)
tablespace users ;

create index emp_empno
on emp (empno)
tablespace users ;

create table review
( empno number,
review_date date,
overall_rating number,
review_file varchar2(500),
comments varchar2(1000)
)
tablespace users ;

create index review_empno
on review (empno)
tablespace users ;

-- create the global temporary table we need
create global temporary table temp_review
on commit delete rows
as
select empno
from review
where 1=0 ;

---------------------------------------------
-- populate the 2 permenant tables with data
---------------------------------------------
-- populate emp with 2000 employees
declare
v_deptno number := 10 ;
v_ename varchar2(50) ;
v_loop_cnt number := 0 ;

begin
-- loop
loop
exit when v_loop_cnt = 2000 ;

-- add to our loop count
v_loop_cnt := v_loop_cnt + 1 ;

v_ename := 'Employyee ' TO_CHAR (v_loop_cnt) ;

-- insert our employee record
insert into emp values (v_loop_cnt, v_deptno, v_ename) ;
end loop ;

commit ;
end ;
/

commit ;

-- populate review 6 reivews for each employee
declare
v_comment varchar2(1000) := 'Put the same comment in for all employees since it does not matter for our example' ;
v_empno number ;
v_file varchar2(500) := '/same/file/for/all/employees.doc' ;
v_review_date date ;
v_loop_cnt number := 0 ;
cursor c_emp is
select empno from emp ;

begin
-- loop though all employees 20 times
loop
exit when v_loop_cnt >= 20 ;

v_loop_cnt := v_loop_cnt + 1 ;

v_review_date := to_date( to_char(sysdate, 'DD-MON') '-' to_char( to_number( to_char(sysdate, 'YYYY')) + v_loop_cnt), 'DD-MON-YYYY') ;

-- open the employee cursor
open c_emp ;

-- loop though all the employees putting a review in for each employeee
loop
fetch c_emp into v_empno ;
exit when c_emp%notfound ;

insert into review values (v_empno, v_review_date, 4, v_file, v_comment) ;
end loop ;

-- close employee cursor
close c_emp ;
end loop ;
end ;
/

commit ;

-- Add a few employess without reviews
insert into emp values (9000, 20, 'Employee 9000') ;
insert into emp values (9001, 20, 'Employee 9001') ;
insert into emp values (9002, 20, 'Employee 9002') ;
insert into emp values (9003, 20, 'Employee 9003') ;
insert into emp values (9004, 20, 'Employee 9004') ;
commit ;

-- get the count of employees
select count(*) from emp ;

-- get the count of reviews
select count(*) from review ;

set timing on

set autotrace on
-- select our data normal, how long does it take
select empno, deptno, ename
from emp
where empno not in (select empno from review) ;
set autotrace off

-- populate the global tempoary table
insert into temp_review
select distinct empno
from review ;

set autotrace on
-- get our results
select empno, deptno, ename
from emp
where
empno not in
(select empno from temp_review) ;
set autotrace off
set timing off

-- commit we are all done
commit ;

-- turn our spooling off
spool off