临时表小结

使用物理表的问题:

1、回滚段很大，大量的insert 后又delete是会占用大量回滚段，要清空表的话用truncate，truncate不会产生回滚段。 回滚段多、大会对数据库会产生很大压力。遇到并发查询多情况下，性能会下降非常高【达梦数据库遇到的问题】
2、 频繁插入数据又删除，会造成高水位问题，表为空，但是占用大量表空间不释放。【Oracle物理表频繁删除会到的问题】

如何创建创建临时表

--基于事务的临时表
create global temporary table test
(
  ID   number
)
on commit delete rows;

--基于session的临时表
create global temporary table test
(
  ID   number
)
on commit preserve rows;

1. 临时表分类

ORACLE临时表有两种类型：会话级的临时表和事务级的临时表。

• 事务级的临时表 ON COMMIT DELETE ROWS

  临时表的默认参数，表示临时表中的数据仅在事物过程（Transaction）中有效，当事物提交（COMMIT）后，临时表的将被自动截断（TRUNCATE）

SQL> CREATE GLOBAL TEMPORARY TABLE TEMPTABLE_TEST

 (

 　　　　ID NUMBER ,

 　　　　NAME VARCHAR2(32)

 ) ON COMMIT DELETE ROWS;

Table created

SQL> INSERT INTO TEMPTABLE_TEST

　　 SELECT 1, 'adaivskenan' FROM DUAL;

1 row inserted

SQL> SELECT * FROM TEMPTABLE_TEST;

ID 　　　　　　　　　　NAME

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

1 adaivskenan

SQL> COMMIT;

Commit complete

SQL> SELECT * FROM TEMPTABLE_TEST;

ID 　　　　　　　　　　NAME

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

• 会话级的临时表 ON COMMIT PRESERVE ROWS

  会话级的临时表的数据和当前会话有关系，当前SESSION不退出的情况下，临时表中的数据就还存在，临时表的数据只有当退出当前SESSION的时候才被截断（TRUNCATE TABLE）

  操作示例：

SQL> CREATE GLOBAL TEMPORARY TABLE TEMPTABLE_TEST
 (

　　 ID NUMBER ,

 　　NAME VARCHAR2(32)

 ) ON COMMIT PRESERVE ROWS;

Table created

SQL> INSERT INTO TEMPTABLE_TEST

    SELECT 1, 'adaivskenan' FROM DUAL;

1 row inserted

SQL> COMMIT;

Commit complete

SQL> SELECT * FROM TEMPTABLE_TEST;

ID 　　　　　　　　NAME

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

1 adaivskenan

SQL> INSERT INTO TEMPTABLE_TEST

 　　SELECT 2, 'adaivskenan' FROM DUAL;

1 row inserted

SQL> ROLLBACK;

Rollback complete

SQL> SELECT * FROM TEMPTABLE_TEST;

ID 　　　　　　　　　　NAME

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

1 　　　　　　　　　　adaivskenan

SQL>
--用sys用户登录数据库，打开SESSION 2
--SELECT * FROM DBA_TABLES WHERE TABLE_NAME='TEMPTABLE_TEST' --可以查到临时表数据
--SELECT * FROM TEMPTABLE_TEST; --查不到数据，即使TEMPTABLE_TEST临时表存在数据。

2. 事务级临时表用途

为了提高查询效率，程序查询存在多选条件时，sql where条件中存在大量的in查询。通过将条件参数插入临时表，通过临时表暂存
条件提示查询效率。

3. 会话级存在的问题

业务系统为提升效率都会使用数据库连接池，使用连接池时连接数据库的session只会归还给连接池。如果使用基于session的临时表，每次操作完表数据库不自动做删除操作，会造成数据累积，导致业务数据查询出现错误。

如果要DROP会话级别临时表，并且其中包含数据时，必须先截断其中的数据。否则会报错。

SQL> DROP TABLE TEMPTABLE_TEST PURGE;
DROP TABLE TEMPTABLE_TEST PURGE
ORA-14452: 试图创建, 更改或删除正在使用的临时表中的索引
SQL> TRUNCATE TABLE TEMPTABLE_TEST;
Table truncated
SQL> DROP TABLE TEMPTABLE_TEST PURGE;
Table dropped