Sybase Developer Asks: How To Create a Temporary Table in Oracle?

Question

I'm familiar with Sybase / SQL server, where I can create a temp. table like this:

SELECT * 
INTO   #temp
FROM   tab1 , 
       tab2 
WHERE  tab1.key = tab2.fkey

SELECT * 
FROM   #temp 
WHERE  field1 = 'value'

#temp only exists for the duration of this session, and can only be seen by me.

I would like to do a similar thing in Oracle, but I'm reading about "global temporary tables", which don't sound like the same thing.

How can I do the same thing in Oracle as I'm doing in Sybase?

Thanks :)

Answer 1

I believe global temporary tables are the same. They will give you private access to a temporary table that dies when the session ends:

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.

After reading the question a few more times I believe it the main difference, and maybe your issue is that the temporary tables persist between sessions. So the exact equivalent is not possible as you would imagine in Oracle it would be something like:

CREATE GLOBAL TEMPORARY TABLE my_temp_table ON COMMIT DELETE ROWS select * from other table;

That table will live until it is dropped even through sessions although the data in it does not. Instead you would need to create the temporary table in advance.

Answer 2

Yes, carson has it right. Global temporary tables are only visible to the session that creates them, and disappear either at the first commit or rollback, or at the end of the session. You can set that when you create the gtt.

Answer 3

A global temporary table is not the same, the definition remains after the end of the session, also the table (but not the data) is visible to all sessions.

If you are writing stored procedures, have you looked into cursors? It's a bit more complicated, but a very efficient and clean way to work with a temporary data set.

Answer 4

Oracle does not provide a direct analogue of this facility. A global temporary table is similar, but it must be created in advance and can be difficult to alter down the line due to locking issues.

Most needs of this nature can be met with cursors or one of the different pl/sql collection types (nested tables, varrays, associative arrays), but none of those can be used as if they were a table. That is, you cannot SELECT from them.

Answer 5

Your first approach ought to be to do this as a single query:

SELECT * 
FROM   
(
SELECT * 
FROM   tab1 , 
       tab2 
WHERE  tab1.key = tab2.fkey
)
WHERE  field1 = 'value';

For very complex situations or where temp# is very large, try a subquery factoring clause, optionally with the materialize hint:

with #temp as
(
SELECT /*+ materialize */ 
       * 
FROM   tab1 , 
       tab2 
WHERE  tab1.key = tab2.fkey
)
SELECT * 
FROM   temp#
WHERE  field1 = 'value';

If that is not helpful, go to the Global Temporary Table method.

Answer 6

The temporary table model is somewhat different in Oracle, and centers around the "CREATE GLOBAL TEMPORARY TABLE.." statement. Temp table definitions are always global, but data is always private to the session, and whether data persists over a commit depends on whether the qualification "on commit preserve rows" or "on commit delete rows" is specified.

I have some Perl scripts and a blogpost that explores the specific behaviour or Oracle temp tables on my blog.