---全域性臨時表建立語法
SQL> create global temporary table t_global_temp(a int)
2 on commit delete rows;

Table created.

---查詢表名
SQL> select table_name from user_tables where table_name='T_GLOBAL_TEMP';

TABLE_NAME
------------------------------------------------------------
T_GLOBAL_TEMP

--查詢表對應的segment
SQL> select segment_name,segment_type from user_segments where segment_name='T_G
LOBAL_TEMP';

no rows selected

---插入資料
SQL> insert into t_global_temp values(1);

1 row created.

SQL> commit;

Commit complete.

--提交查詢無記錄
SQL> select * from t_global_temp;

no rows selected

--再次查詢segment無記錄,原因:建立全域性臨時表指定on commit delete rows一提交即清表
SQL> select segment_name,segment_type from user_segments where segment_name='T_G
LOBAL_TEMP';

no rows selected

SQL> insert into t_global_temp values(1);

1 row created.

--插入不提交即可查詢到記錄
SQL> select segment_name,segment_type from user_segments where segment_name='T_G
LOBAL_TEMP';

no rows selected

--提交與否皆不佔用儲存空間,引申問題:哪全域性臨時表的資料儲存在哪兒呢?
SQL> select segment_name,segment_type from user_segments where segment_name='T_G
LOBAL_TEMP';

no rows selected

---以基於會話方式建立全域性臨時表
SQL> create global temporary table t_global_temp(a int) on commit preserve rows;

Table created.

SQL> insert into t_global_temp values(1);

1 row created.

---提交前查詢
SQL> select * from t_global_temp;

A
----------
1

SQL> select segment_name,segment_type from user_segments where segment_name='T_G
LOBAL_TEMP';

no rows selected

SQL> commit;

Commit complete.

--提交後查詢
SQL> select * from t_global_temp;

A
----------
1

---附上提交前後在另一會話查全域性臨時表測試,全域性臨時表的資料僅在當前會話可見
SQL> select * from t_global_temp;

no rows selected

SQL> /

no rows selected

SQL> desc t_global_temp;
Name Null? Type
----------------------------------------- -------- -----------------------

A NUMBER(38)

---測試全域性臨時表的alter table及create index及alter index
--如全域性臨時表正在使用alter table不能執行
SQL> alter table t_global_temp add b int;
alter table t_global_temp add b int
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use

---只有退出當前會話
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\123>sqlplus scott/system

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 9 16:07:10 2013

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

---再次alter table即可成功
SQL> alter table t_global_temp add b int;

Table altered.

---在全域性臨時表構建索引
SQL> create index idx_temp on t_global_temp(a);

Index created.

--刪除全域性臨時表索引
SQL> drop index idx_temp;

Index dropped.

SQL> select count(*) from t_global_temp;

COUNT(*)
----------
0

SQL> insert into t_global_temp select 1,3 from dual connect by level<3e5;

299999 rows created.

SQL> commit;

Commit complete.

---收集全域性臨時表的統計資訊
SQL> exec dbms_stats.gather_table_stats(user,'t_global_temp');

PL/SQL procedure successfully completed.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

Plan hash value: 62698482

----------------------------------------------------------------------------
| Id | Operation          | Name          | Rows | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |   257   (4)| 00:00:04 |
|   1 | SORT AGGREGATE    |               |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T_GLOBAL_TEMP |   599K|   257   (4)| 00:00:04 |
----------------------------------------------------------------------------

9 rows selected.

---僅插一條a值888888888888888888888的記錄到全域性臨時表
SQL> insert into t_global_temp select 888888888888888888888,1 from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> explain plan for select count(*) from t_global_temp where a=88888888888888
888888;

Explained.

--執行計劃顯示走了索引
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

Plan hash value: 1743356947

------------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     3 |     3   (0)| 00:00:01 |
|   1 | SORT AGGREGATE   |          |     1 |     3 |            |          |
|* 2 |   INDEX RANGE SCAN| IDX_TEMP |     1 |     3 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

---------------------------------------------------

2 - access("A"=888888888888888888888)

14 rows selected.

----全域性臨時表的操作限制

----不能分割槽,不能集簇化,不能iot化
Temporary tables cannot be partitioned, clustered, or index organized.

---不能指定外來鍵約束
You cannot specify any foreign key constraints on temporary tables.

---不能包含nested table column
Temporary tables cannot contain columns of nested table.

----不能指定lob_storage_clause的引數:tablespace,storage_clause or logging_clause
You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, or logging_clause.

---不能啟用並行update,delte,merge
Parallel UPDATE, DELETE and MERGE are not supported for temporary tables.

---在segment_atrributes_clause子句中,唯一可指定的引數是:tablespace
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.

小結:全域性臨時表特別適用於儲存中轉結果,即臨時計算的結果,非最終結果;
可用於報表統計儲存過程.

create table進階學習(二)_全域性臨時表_global temporary table

相關文章