Sybase developer asks: how to create a temporary table in Oracle?

Question

I am familiar with the Sybase / SQL server where I can create temp. the table is as follows:

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

#temp exists only during the whole session, and it can only be seen.

I would like to do this in Oracle, but I read about "global temporary tables" that do not look like the same thing.

How can I do the same in Oracle as in Sybase?

Thanks:)

Answer 1

Your first approach should be to do this as a single request:

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

In very difficult situations or where temp # is very large, try the factorization option for the subquery, optionally with the help materialize:

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

If this does not help, go to the Global temporary table method.

Answer 2

The global temporary table does not match, the definition remains after the end of the session, and the table (but not the data) is visible to all sessions.

If you write stored procedures, have you looked at cursors? This is a bit more complicated, but a very efficient and clean way to work with a temporary dataset.

Answer 3

Oracle does not provide a direct counterpart to this object. The global temporary table is similar to it, but it needs to be created in advance, and it will be difficult to change due to locking problems.

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 them can be used as if they were a table. That is, you cannot choose from them.

Answer 4

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

The data in the global temporary table is private, so the data inserted by the session may only be available for that session. Session-specific rows in the global temporary table can be saved for the entire session or only for the current transaction. The ON COMMIT DELETE ROWS clause indicates that data should be deleted at the end of the transaction.

After reading the question a few more times, I think this is the main difference, and maybe your problem is that temporary tables are stored between sessions. Thus, an exact equivalent is not possible, as you might imagine in Oracle, it would be something like:

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

This table will live until it is discarded even through sessions, although there is no data in it. Instead, you will need to create a temporary table in advance.

Answer 5

The temporary table model is slightly different in Oracle and is centered around the CREATE GLOBAL TEMPORARY TABLE statement ... Temp table definitions are always global, but the data is always private to the session and whether the data is stored over the commit, depending on whether the qualification is set to “on commit keep rows "or" on commit rows rows ".

I have some Perl scripts and a blog post that explores specific behavior or Oracle temp tables on my blog .

Answer 6

Yes, Carson is right. Global temporary tables are visible only for the session that creates them, and disappear either at the first commit, or in rollback or at the end of the session. You can set this when creating gtt.