Global Temporary Table in Oracle(原创)

ltptliitur

　　OVERVIEW OF GLOBAL TEMPORARY
Specify GLOBAL TEMPORARY to indicate that the table is temporary and that its definition is visible to all sessions with appropriate privileges. The data in a temporary table is visible only to the session that inserts the data into the table.
When you first create a temporary table, its table metadata is stored in the data dictionary, but no space is allocated for table data. Space is allocated for the table segment at the time of the first DML operation on the table. The temporary table definition persists in the same way as the definitions of regular tables, but the table segment and any data the table contains are either session-specific or transaction-specific data. You specify whether the table segment and data are session- or transaction-specific with the ON COMMIT keywords.
You can perform DDL operations (such as ALTER TABLE, DROP TABLE, CREATE INDEX) on a temporary table only when no session is bound to it. A session becomes bound to a temporary table by performing an INSERT operation on the table. A session becomes unbound to the temporary table by issuing a TRUNCATE statement or at session termination, or, for a transaction-specific temporary table, by issuing a COMMIT or ROLLBACK statement.
Restrictions on Temporary Tables Temporary tables are subject to the following restrictions:

• Temporary tables cannot be partitioned, clustered, or index organized.
  
• You cannot specify any foreign key constraints on temporary tables.
  
• Temporary tables cannot contain columns of nested table.
  
• You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, or logging_clause.
  
• Parallel UPDATE, DELETE and MERGE are not supported for temporary tables. Temporary tables only support INSERT parallelly .
  
• The only part of the segment_attributes_clause you can specify for a temporary table is TABLESPACE, which allows you to specify a single temporary tablespace.  
  
• Distributed transactions are not supported for temporary tables.
  

　　Unlike temporary tables in some other relational databases, when you create a temporary table in an Oracle database, you create a static table definition. The temporary table is a persistent object described in the data dictionary, but appears empty until your session inserts data into the table. You create a temporary table for the database itself, not for every PL/SQL stored procedure.
Locks, Index
　　Because temporary tables are statically defined, you can create indexes for them with the CREATE INDEX statement. Indexes created on temporary tables are also temporary. The data in the index has the same session or transaction scope as the data in the temporary table. You can also create a view or trigger on a temporary table.
　　Data in a temporary table is private to the session. Each session can only see and modify its own data. DML locks are not acquired on the data of the temporary tables. The LOCK statement has no effect on a temporary table, because each session has its own private data.
A TRUNCATE statement issued on a session-specific temporary table truncates data in its own session. It does not truncate the data of other sessions that are using the same table.
　　DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated. Data from the temporary table is automatically dropped in the case of session termination, either when the user logs off or when the session terminates abnormally such as during a session or instance failure.
Oracle utilities can export and import the definition of a temporary table. However, no data rows are exported even if you use the ROWS clause. Similarly, you can replicate the definition of a temporary table, but you cannot replicate its data.
Creation of Global Temporary Tables
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.
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
      column1  NUMBER,
      column2  NUMBER
    ) ON COMMIT DELETE ROWS;
In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.
　　Tips, you can't gather table stats for transaction-specific temporary table, as it starts a new transaction when you gather table stats
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
      column1  NUMBER,
      column2  NUMBER
    ) ON COMMIT PRESERVE ROWS;
If you ommit "on commit " clause, Oracle will create transaction-sepecif by default.
　　you can query the temporary table info by below sql:
　　select TABLE_NAME, TEMPORARY, DURATION from dba_tables
where table_name in ('TEMP_1', 'TEMP_2');
TABLE_NAME                     TEM DURATION
------------------------------ --- ---------------------------
TEMP_2                         Y   SYS$SESSION
TEMP_1                         Y   SYS$TRANSACTION
　　You can't query the temporary table segment info in dba_segments dictionary view. Actually I don't know how to query the temporary table segment info.
　　Less Redo logs on Temporary Table
　　If you insert into a global temporary table you'll generate
a) undo for the table - but this is trivial in general because the undo for a conventional path insert is simply "delete+rowid" - it is very small.
b) undo for the indexes - this could be non-trivial in size depending on the size of the indexed columns and the number of indexes.
However, that said, it will generate *less* redo than a conventional path insert into a "normal" table.
Check the below example for details
ops$tkyte%ORA11GR2> create table stage as select * from all_objects;
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create global temporary table gtt on commit delete rows
  2  as
  3  select * from all_objects where 1=0;
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table t as select * from all_objects where 1=0;
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> column value new_val REDO
ops$tkyte%ORA11GR2> select a.name, b.value from v$statname a, v$mystat b where
a.statistic# = b.statistic# and a.name = 'redo size';
NAME                        VALUE
---------------------- ----------
redo size                36495320
ops$tkyte%ORA11GR2> insert into gtt select * from stage;
72887 rows created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> select a.name, b.value, b.value-&REDO diff from v$statname a,
v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
NAME                        VALUE       DIFF
---------------------- ---------- ----------
redo size                36911000     415680
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select a.name, b.value from v$statname a, v$mystat b where
a.statistic# = b.statistic# and a.name = 'redo size';
NAME                        VALUE
---------------------- ----------
redo size                36911000
ops$tkyte%ORA11GR2> insert into t select * from stage;
72887 rows created.
ops$tkyte%ORA11GR2> delete from t;
72887 rows deleted.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> select a.name, b.value, b.value-&REDO diff from v$statname a,
v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
NAME                        VALUE       DIFF
---------------------- ---------- ----------
redo size                73020096   36109096
well, would you rather have 415k of redo or 36m or redo?
Now, if we index something:
ops$tkyte%ORA11GR2> create index gtt_idx on gtt(owner,object_type,object_name);
Index created.
ops$tkyte%ORA11GR2> create index t_idx on t(owner,object_type,object_name);
Index created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> column value new_val REDO
ops$tkyte%ORA11GR2> select a.name, b.value from v$statname a, v$mystat b where
a.statistic# = b.statistic# and a.name = 'redo size';
NAME                        VALUE
---------------------- ----------
redo size                73343908
ops$tkyte%ORA11GR2> insert into gtt select * from stage;
72887 rows created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> select a.name, b.value, b.value-&REDO diff from v$statname a,
v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
NAME                        VALUE       DIFF
---------------------- ---------- ----------
redo size                92151752   18807844
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select a.name, b.value from v$statname a, v$mystat b where
a.statistic# = b.statistic# and a.name = 'redo size';
NAME                        VALUE
---------------------- ----------
redo size                92151752
ops$tkyte%ORA11GR2> insert into t select * from stage;
72887 rows created.
ops$tkyte%ORA11GR2> delete from t;
72887 rows deleted.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> select a.name, b.value, b.value-&REDO diff from v$statname a,
v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
NAME                        VALUE       DIFF
---------------------- ---------- ----------
redo size               177385212   85233460
that is 18m versus 85m - which would you rather have? and even if you use truncate (take away the multiuser aspect) you would have:
ops$tkyte%ORA11GR2> insert into t select * from stage;
72887 rows created.
ops$tkyte%ORA11GR2> truncate table t;
Table truncated.
ops$tkyte%ORA11GR2> select a.name, b.value, b.value-&REDO diff from v$statname a,
v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
NAME                        VALUE       DIFF
---------------------- ---------- ----------
redo size               236454188   39883836
that was the index example - almost 40mb.
the facts surrounding a global temporary table are:
a) they generate LESS redo - in most cases *significantly* less redo (no indexes) and if you can use a session based global temporary table - they can generate almost NO UNDO using insert /*+ append */
b) they do not require an expensive delete operation
c) they require less work on the part of the developer since they clean themselves out.
d) to use them with updates and deletes is what I would call "atypical" - not the normal use. most of the use is 1) insert into them, 2) query them, 3) commit and clear them out.
when you start updating and deleting - you start generating gobs of undo as that cannot be done using direct path and those two things generate the most undo possible. A delete has to record the entire row in the undo, and update has to record as much of the row that was modified.
So, if you insert, query, commit - the "normal" use pattern - they make sense.
If you need the self cleansing ability - they make sense (even if you update them)
If you are deleting from them (the worst of the worst), I'd start to question your logic.


　　参考至：http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7002.htm#SQLRF54449
　　http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT88817
　　http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm#sthref769
　　http://www.oracle-base.com/articles/misc/temporary-tables.php
　　https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4817636200346963925
　　本文原创，转载请注明出处、作者
　　如有错误，欢迎指正
　　邮箱:czmcj@163.com