Oracle中的临时表Temporary Table
Oracle中的临时表（Temporary Table）是一种特殊类型的表，用于存储临时数据，这些数据在会话结束或事务提交后会自动删除。Oracle数据库提供了两种主要的临时表类型：事务级全局临时表和会话级全局临时表。
全局临时表（Global Temporary Table）
全局临时表是Oracle数据库中常用的临时表类型，它具有以下特点：
- 临时性：全局临时表中的数据在会话结束或事务提交后（取决于ON COMMIT子句的设置）自动删除。
- 私有性：尽管名为“全局”，但全局临时表中的数据对于创建它的会话是私有的，其他会话无法访问。
- 性能优势：全局临时表使用内存或临时表空间存储数据，相比于在磁盘上进行操作，速度更快，可以提高查询性能，并减少数据库资源的占用。
- 减少锁等待时间：由于全局临时表的数据只在当前会话中存在，不会被其他会话访问，因此减少了锁等待时间，提高了并发访问性能。
创建全局临时表的语法：
CREATE GLOBAL TEMPORARY TABLE table_name (
column1 datatype [constraint],
column2 datatype [constraint],
...
columnN datatype [constraint]
) ON COMMIT { DELETE ROWS | PRESERVE ROWS };
table_name：临时表的名称。column1, column2, ..., columnN：表的列名。datatype：列的数据类型。constraint：可选的约束条件。ON COMMIT { DELETE ROWS | PRESERVE ROWS }：指定在事务提交时如何处理临时表中的数据（事务级/会话级）。DELETE ROWS是临时表的默认参数,表示在事务提交后删除数据，临时表中的数据仅在事物过程（Transaction）中有效，当事物提交（COMMIT）后，临时表的暂时段将被自动截断（TRUNCATE），但是临时表的结构 以及元数据还存储在用户的数据字典中。如果临时表完成它的使命后，最好删除临时表，否则数据库会残留很多临时表的表结构和元数据。PRESERVE ROWS它表示临时表的内容可以跨事物而存在，不过，当该会话结束时，临时表的暂时段将随着会话的结束而被丢弃，临时表中的数据自然也就随之丢弃，但是临时表的结构以及元数据还存储在用户的数据字典中。如果临时表完成它的使命后，最好删除临时表，否则数据库会残留很多临时表的表结构和元数据。。
会话/事务临时表（概念上的理解）
虽然Oracle官方术语中并不直接称为“会话临时表”，但全局临时表在会话级别的行为可以被视为会话临时表的一种实现。即，全局临时表在会话结束时（如果ON COMMIT设置为PRESERVE ROWS且会话正常结束）或事务提交后（如果ON COMMIT设置为DELETE ROWS）自动删除数据，从而实现了数据的会话级临时性。
示例
-- 全局临时表 使用on commit delete rows选项（事务级临时表，事务提交，数据删除，保留临时比表结构）
HR@orcl> create global temporary table t_temp_emp on commit delete rows as select employee_id,last_name,salary from employees;
Table created.
HR@orcl> select * from t_temp_emp;
no rows selected
-- 全局临时表 使用默认选项（事务级临时表，事务提交，数据删除，保留临时比表结构）
HR@orcl> create global temporary table t_temp_emp_1 as select employee_id,last_name,salary from employees;
Table created.
HR@orcl> select * from t_temp_emp_1;
no rows selected
-- 事务级临时表，事务不提交或回滚，则临时表数据不删除
HR@orcl> insert into t_temp_emp select employee_id,last_name,salary from employees;
107 rows created.
HR@orcl> select * from t_temp_emp;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
100 King 24000
101 Kochhar 17000
102 De Haan 17000
103 Hunold 9000
104 Ernst 6000
................. 省略中间行内容 ................
205 Higgins 12008
206 Gietz 8300
107 rows selected.
-- 提交事务，则数据删除
HR@orcl> commit;
Commit complete.
HR@orcl> select * from t_temp_emp;
no rows selected
-- 创建会话级临时表
HR@orcl> create global temporary table t_temp_emp_session on commit preserve rows as select employee_id,last_name,salary from employees;
Table created.
-- 当前会话保留数据
HR@orcl> select * from t_temp_emp_session;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
100 King 24000
101 Kochhar 17000
102 De Haan 17000
103 Hunold 9000
104 Ernst 6000
................. 省略中间行内容 ................
205 Higgins 12008
206 Gietz 8300
107 rows selected.
-- 切换会话
HR@orcl> conn / as sysdba
Connected.
-- 再次hr用户登陆
SYS@orcl> conn hr/hr@ORCL
Connected.
-- 发现数据在会话切换(创建临时表的当前会话结束,数据自然也就随之丢弃)
HR@ORCL> select * from t_temp_emp_session;
no rows selected
- 查看临时表空间中段的情况，可以查看v$temp_extent_map
- 查看临时表空间的文件：v$tempfile;
- 查看sql使用临时块的情况：v$tempseg_usage
- 查看临时块的状态v$tempstat
- 从DBA_TABLES/USER_TABLES视图的DURATION列来查询是 on commit delete rows / on commit presever rows
HR@ORCL> col table_name format a30
HR@ORCL> select table_name,tablespace_name,DECODE(DURATION,'SYS$SESSION','会话级','SYS$TRANSACTION','事务级') T_TYPE from user_tables where temporary='Y';
TABLE_NAME TABLESPACE_NAME T_TYPE
------------------------------ ------------------------------ ---------
T_TEMP_EMP_1 事务级
T_TEMP_EMP 事务级
T_TEMP_EMP_SESSION 会话级
应用场景
全局临时表在Oracle数据库中有广泛的应用场景，包括但不限于：
- 临时存储计算结果：在执行复杂的查询或计算时，可以将中间结果存储在全局临时表中，以便后续查询或处理。
- 优化性能：通过减少磁盘I/O操作和提高内存访问速度，全局临时表可以显著提高查询性能。
- 会话级数据管理：全局临时表可以用于存储用户特定的参数、上下文信息或临时状态，确保数据的隔离性和独立性。
总之，Oracle中的全局临时表是一种强大的工具，它提供了灵活、高效和安全地处理临时数据的机制，满足了各种数据库应用场景的需求。