临时表的类别
临时表分为两种:事物型临时表——在事物完成后会立即truncate表,会话型临时表——在事物完成后会暂时保存数据,在会话中止或者显示提交truncate等语句才会删除数据。
ON COMMIT Setting
Implications
DELETE ROWS
This creates a temporary table that is transaction
specific. A session becomes bound to the temporary table with a transactions
first insert into the table. The binding goes away at the end of the
transaction. The database truncates the table (delete all rows) after each
commit.
PRESERVE ROWS
This creates a temporary table that is session specific. A
session gets bound to the temporary table with the first insert into the
table in the session. This binding goes away at the end of the session or by
issuing aTRUNCATEof
the table in the session. The database truncates the table when you terminate
the session.
创建语句
创建临时表的语法如下
CREATE GLOBAL TEMPORARY TABLE temp_tab_name
([column datatype [,column datatype])
[ON COMMIT {DELETE | PRESERVE} ROWS]
[TALBESPACE tbs];
在默认情况下,临时表存在与默认临时表空间中,也可以指定到永久表空间中或者创建一个新的临时表空间,把临时表指定到这个新临时表空间中。前面的博文已经讲诉了创建另一个临时表空间,这里就不再赘述。
我们使用SCOTT用户创建一个事物临时表和会话临时表
CREATE
GLOBAL TEMPORARY TABLE temp_trans
(startdate DATE,
class CHAR(20))
ON COMMIT DELETE ROWS;
CREATE
GLOBAL TEMPORARY TABLE temp_sess
(startdate DATE,
class CHAR(20))
ON COMMIT PRESERVE ROWS;
在创建完临时表后,查看表的属性
SQL>
show user;
User
is "scott"
SQL>
select table_name,TEMPORARY,DURATION,LOGGING from user_tables where table_name
like 'TEMP_%';
TABLE_NAME TEMPORARY DURATION LOGGING
------------------------------
--------- --------------- -------
TEMP_SESS Y SYS$SESSION NO
TEMP_TRANS Y SYS$TRANSACTION NO
分别给2个表插入数据
SQL>
insert into temp_trans values(sysdate,'transaction');
1
row inserted
SQL>
insert into temp_sess values(sysdate,'session');
1
row inserted
SQL>
select * from temp_trans;
STARTDATE CLASS
-----------
------------------------------------------------------------
2017-6-15
上 transaction
SQL>
select * from temp_sess;
STARTDATE CLASS
-----------
------------------------------------------------------------
2017-6-15
上 session
SQL>
commit;
Commit
complete
SQL>
select * from temp_trans;
STARTDATE CLASS
-----------
------------------------------------------------------------
SQL>
select * from temp_sess;
STARTDATE CLASS
-----------
------------------------------------------------------------
2017-6-15
上 session
以上例子可以看出,事物临时表在事物提交后表内的数据不会保存,而会话临时表在事物提交后表内的数据会保存。
sqlplus
scott/tiger
SQL*Plus:
Release 11.2.0.4.0 Production on Thu Jun 15 10:52:31 2017
Copyright
(c) 1982, 2013, Oracle. All rights
reserved.
Connected
to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With
the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Data
Mining and Real Application Testing options
SQL>
select * from temp_sess;
no
rows selected
用其他会话登录SCOTT,便看不到表内的数据
SQL>
drop table temp_sess;
drop
table temp_sess
*
ERROR
at line 1:
ORA-14452:
attempt to create, alter or drop an index on temporary table already
in
use
SQL>
truncate table temp_sess;
Table
truncated.
SQL>
drop table temp_sess;
Table
dropped.
DDL operations (exceptTRUNCATE) are allowed on an existing temporary table only if no session is
currently bound to that temporary table.
在删除表时,如果表内有数据,且session存在,是无法删除的。
使用场景
在一般情况下,临时表的数据存在于各自的PGA会话中,不涉及磁盘活动,所以针对临时表的访问是比放在永久表空间中的表快。临时表的DML语句不会生成重做数据,也没有这个必要。临时表也可以创建索引,其索引跟临时表属性一致,临时表是事物型索引就是事物型,临时表是会话型索引就是会话型。
如果表数据的结果集希望被存储在内存中,在会话期间表的行记录能被保存在内存中,会话结束后能被删除数据的表使用临时表就是非常合适的。