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

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.