临时表小结

/ 随笔 / 0 条评论 / 50浏览

如何创建创建临时表

--基于事务的临时表
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临时表有两种类型:会话级的临时表和事务级的临时表。

  临时表的默认参数,表示临时表中的数据仅在事物过程(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

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

  会话级的临时表的数据和当前会话有关系,当前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临时表存在数据。

  1. 事务级临时表用途

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

  1. 会话级存在的问题

  业务系统为提升效率都会使用数据库连接池,使用连接池时连接数据库的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