Re: Oracle replacement for Sybase #temp tables

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Wed, 05 Jan 2000 21:01:40 +0800
Message-ID: <387340B4.518B@yahoo.com>

Kristy Kallback-Rose wrote:
>
> Tom,
> Just to make sure I undertand your suggestion. Are you recommending that a
> pool of names be used ranging from temp_0001 - tempnnnn? The only problem with
> this is that we may want to track the owner of a temp table to a session. That's
> why the idea of tacking on the session_id to the table name is appealing. If we
> just hand out numbered tables I'm not sure how we could track the owner for
> troubleshooting. I'm new to Oracle so let me know if I'm missing something here.
>
> Your suggestion for clean up makes sense.
>
> Thanks,
> Kristy
>
> Tom Best wrote:
>
> > You could use a sequence generator to create the temp table. Call it
> > temp_nnnnn or something. If you need a cleanup routine, it may be easiest to
> > query the system catalog of all tables starting with "temp_" and drop each one
> > in a loop.
> >
> > You could also use a random number generator. We use this, only because we
> > had to be compatible with about 8 different DBMSs.
> >
> > Tom Best
> >
> > Kristy Kallback-Rose wrote:
> >
> > > We are in the process of converting to Oracle from Sybase. Under Sybase we
> > > had used #temp tables quite a bit (within SQRs, and in the SQL for
> > > datagroup builds to avoid complex joins, for example) and need a
> > > replacement for them in Oracle.
> > >
> > > Oracle's global temporary tables aren't quite ready for prime-time yet, in
> > > that they have some optimizer problems. I don't want to start a discussion
> > > about GTTs, we've already decided not to use them. Rather I'm hoping to
> > > hear from someone who used Sybases's #temp tables and is now using, for
> > > lack of a better term, 'permanent temporary tables' in Oracle with
> > > something like sessionid key to keep the table name unique. It is a
> > > requirement, at least for the SQRs, that the table name be unique.
> > >
> > > If someone does have this type of experience behind them I'd be glad to
> > > provide more details. You can reply to this post or via e-mail.
> > >
> > > Thanks,
> > > Kristy
> > > --
> > > Kristy Kallback-Rose
> > > Information Access & Repository Services Team
> > > University Information Technology Services
> > > Indiana University, Bloomington

This idea from T.Kyte and far superior to have multiple tables ...

new suggestion:

create the 'temp table' once per database. Make it a permanent table.
never drop the table
use the builtin multi-versioning to do your work...

code the procedure as such:

tkyte_at_8.0> create or replace package types   2 as
  3 type rc is ref cursor;
  4 end;
  5 /

Package created.

tkyte_at_8.0>
tkyte_at_8.0> create table TEMP_TABLE storage ( freelists 20 ) as select * from emp
where 1=0
2 /

Table created.
(freelists to let lots of people insert concurrently with minimum wait for space
requests)

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> create or replace procedure get_result_set( p_cursor in out

types.rc
)
2 is

  3      exec_cursor     integer default dbms_sql.open_cursor;
  4      rows_processed  number  default 0;
  5  begin
  6      dbms_sql.parse(exec_cursor,
  7                    'insert into TEMP_TABLE select * from emp where

rownum
<6',

  8                     dbms_sql.native );
  9      rows_processed := dbms_sql.execute(exec_cursor);
 10      dbms_sql.close_cursor( exec_cursor );
 11  
 11      open p_cursor for select * from TEMP_TABLE;
 12  
 12      delete from TEMP_TABLE;

13 end;
14 /

Procedure created.

tkyte_at_8.0> 
tkyte_at_8.0> variable x refcursor
tkyte_at_8.0> exec get_result_set( :x )

PL/SQL procedure successfully completed.

tkyte_at_8.0> print x

EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO

---------- ---------- --------- ---------- --------- ----------
----------
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600

900
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

7566 JONES ANALYST 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

(you could use a static insert in the procedure if you want as well -- I used
dynamic insert to show that you can).....

the concept here is:

o everyone uses the same table (no more DDL in the procedure - much much faster).

o concurrency is not an issue -- no one will ever block on inserts into temp
table. No one will ever see anyone elses data -- never.

o outside of the procedure TEMP_TABLE always appears empty. if the caller
commits, the delete got rid of all data. if the caller rollsback -- the insert
never happened anyway.

o the ref cursor result set is 'preordained' at the time the ref cursor is
opened. Using our read consistency -- the result set is preserved in the ref
cursor (even though you deleted all of the rows in the table). As long as the
caller of this procedure does not commit or rollback -- the result set will be
available to the ref cursor. If they do commit or rollback they MIGHT get
ORA-1555 snapshot too old if they wait too long to fetch from the cursor.

You might consider truncating this table every now and again to lower the high
water mark.

In Oracle8i, release 8.1, you will replace the "create table" with "create
global temporary table .... on commit delete rows" and you could recode the
procedure to be simply:

create or replace procedure get_result_set( p_cursor in out types.rc ) is
begin

execute immediate 'insert into TEMP_TABLE select * from emp where rownum <6';

open p_cursor for select * from TEMP_TABLE; end;
/

to achieve the same result.

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Wed Jan 05 2000 - 07:01:40 CST