After Work
The best club in Chicago
for Chicago Singles is
Highlife Adventures!
Kyoto Sushi
in Chicago, on Lincoln Ave.
Moody's Pub
in Chicago, has great burgers
Skylark
in Chicago, on Halsted .. excellent beer selection!
|
Oracle
»
PL-SQL
»
DDL
»
Performance
Global Temporary Tables
Global temporary tables in Oracle.
Potential usage: Similar in some ways to Sybase temporary tables,
global temp tables can be used to store
data in a temporary fashion, for use within the current transaction.
Syntax Sample:
create global temporary table zz_test123 (
rec_id number not null,
rec_data number not null
);
Sample session:
SQL>
SQL>
SQL> insert into zz_test123 values (123, 7777) ;
1 row created.
SQL> insert into zz_test123 values (123, 8888 ) ;
1 row created.
SQL>
SQL> select * from zz_test123 ;
REC_ID REC_DATA
---------- ----------
123 7777
123 8888
SQL>
SQL>
SQL> commit ;
Commit complete.
SQL>
SQL> select * from zz_test123 ;
no rows selected
What happened? When you commit, the rows disappear.
Also: If I exit SQL plus .. and go back in, the table is still there!
SQL>
SQL> select * from zz_test123 ;
no rows selected
TABLESPACE details: These tables live in the TEMP tablespace. Look at this curious output.
SQL> select table_name, tablespace_name from user_tables where table_name like 'ZZ%' ;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
ZZ_TEST123
Note that there are two options for data persistence:
on commit delete rows and
on commit preserve rows
These are specified at the end of the CREATE command, e.g.
Syntax: create global temporary table zz_test123 (
rec_id number not null,
rec_data number not null
)
on commit {preserve|delete| rows;
With the "preserve" option, data persists until the end of the login session. The table, as you note, continues to exist across sessions.
Global Temporary Tables can also be indexed - BUT, you cannot run DBMS stats on them.
|
|
|
|
