Oracle全局临时表

目前所有使用Oracle作为数据库支撑平台的应用，大部分是数据量比较庞大的系统，即表的数据量级一般情况下都是在百万级以上。当然，在Oracle中创建分区是一种不错的选择，但是当发现应用有多张表关联的时候，并且这些表大部分都比较庞大，而关联的时候发现其中的某一张或者某几张表关联之后得到的结果集非常小，并且查询得到这个结果集的速度非常快，那么这个时候考虑在Oracle中创建“临时表”。

在创建数据表的时候，如果没有特殊地指明，那么创建的表是一个永久的关系型表，也就是说，这个表中对应的数据，除非是显式地删除，否则表中的数据是永远都存在的。相对应的，在Oracle数据库中，还有一种类型的表，叫做临时表。这个临时表跟永久表最大的区别就是表中的数据不会永远地存在。当一个会话结束或者事务结束的时候，这个临时表中的数据，不用用户自己删除，数据库自己会自动清除。

Oracle的临时表创建之后基本不占用表空间，如果没有指定临时表（包括临时表的索引）存放的表空间，那么插入到临时表的数据是存放在Oracle系统的默认临时表空间中（TEMP），一个系统可能有多个临时表空间。临时表的数据只能存放在临时表空间中。

临时表的数据只是在事务期间存在，对于会话类型的临时表，数据在会话期间存在，会话的数据对于当前会话私有，每个会话只能看到并修改自己的数据。DML锁不会加到临时表的数据上。可以对临时表创建索引、视图、触发器，可以用exp和imp工具导入导出表的定义，但是不能导出数据。

（一）临时表的特点

l 多用户操作的独立性：对于使用同一张临时表的不同用户，Oracle都会分配一个独立的TEMP SEGMENT，这样就避免了多个用户在对同一张临时表操作时发生交叉，从而保证了多个用户操作的并发性和独立性。

l 数据的临时性：既然是临时表，顾名思义，存放在该表中的数据是临时性的。Oracle根据创建临时表时指定的参数（ON COMMIT DELETE ROWS /ON COMMIT PRESERVE ROWS），自动将数据TRUNCATE掉。

（二）临时表的分类

Oracle数据库根据临时表的性质不同，可以分为事务临时表（ON COMMIT DELETE ROWS）与会话临时表（ON COMMIT PRESERVE ROWS）。

1、事务临时表

事务临时表是指数据只有在当前事务内有效，该临时表与事务相关，当进行事务提交或者事务回滚的时候，临时表中的数据将自动被清空，其它的内容和会话级的临时表的一致（包括退出SESSION的时候，事务级的临时表的数据也会被清空）。一般情况下，如果在创建数据表的时候，没有特殊指明表是会话临时表的话，那么该表默认为事务临时表。

以下三种情况下，事务临时表中的数据就会被清空：

①　提交事务（COMMIT）

②　回滚事务（ROLLBACK）

③　退出SESSION

创建事务临时表的语法如下所示：

CREATE GLOBAL TEMPORARY TABLE TABLE_NAME

(COL1 TYPE1,COL2 TYPE2...)

[ON COMMIT DELETE ROWS];

事务临时表示例如下所示：

SYS@lhrdb> CREATE GLOBAL TEMPORARY TABLE CGTT_DELETE_LHR AS SELECT * FROM SCOTT.EMP WHERE 1=2;

Table created.

SYS@lhrdb> INSERT INTO CGTT_DELETE_LHR SELECT * FROM SCOTT.EMP WHERE ROWNUM<=5;

5 rows created.

SYS@lhrdb> SELECT * FROM CGTT_DELETE_LHR;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30

7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30

7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20

7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30

SYS@lhrdb> COMMIT;

Commit complete.

SYS@lhrdb> SELECT * FROM CGTT_DELETE_LHR;

no rows selected

从示例中可以看到，当执行完COMMIT后，当前会话就看不到数据了。

2、会话临时表

会话临时表，顾名思义，是指数据只在当前会话内是有效的临时表。关闭当前会话或者进行新的连接之后，数据表中的内容就会被清除。

CREATE GLOBAL TEMPORARY TABLE TABLE_NAME

(COL1 TYPE1,COL2 TYPE2...)

ON COMMIT PRESERVE ROWS;

会话临时表示例如下所示：

SYS@lhrdb> CREATE GLOBAL TEMPORARY TABLE CGTT_PRESERVE_LHR ON COMMIT PRESERVE ROWS AS SELECT * FROM SCOTT.EMP WHERE 1=2;

Table created.

SYS@lhrdb> INSERT INTO CGTT_PRESERVE_LHR SELECT * FROM SCOTT.EMP WHERE ROWNUM<=5;

5 rows created.

SYS@lhrdb> SELECT * FROM CGTT_PRESERVE_LHR;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30

7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30

7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20

7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30

SYS@lhrdb> COMMIT;

Commit complete.

SYS@lhrdb> SELECT * FROM CGTT_PRESERVE_LHR;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30

7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30

7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20

7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30

SYS@lhrdb> CONN / AS SYSDBA

Connected.

SYS@lhrdb> SELECT * FROM CGTT_PRESERVE_LHR;

no rows selected

从示例中可以看到，当执行完COMMIT后，数据依然存在，但是当重新连接会话后，数据就被清空了。

查看一张表是否临时表，可以从DBA_TABLES视图的DURATION列来查询：

SELECT UT.TABLE_NAME,

UT.TABLESPACE_NAME,

UT.TEMPORARY,

DECODE(UT.DURATION,'SYS$SESSION','会话级','SYS$TRANSACTION','事务级') 临时表类型

FROM DBA_TABLES UT

WHERE UT.TEMPORARY = 'Y'

AND UT.TABLE_NAME LIKE '%CGTT%';

TABLE_NAME TABLESPACE_NAME T TYPE

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

CGTT_DELETE_LHR Y 事务级

CGTT_PRESERVE_LHR Y 会话级

会话临时表与事务临时表主要的差异就在于删除数据的时机不同。事务临时表是在事务提交或回滚的时候清除数据，而会话临时表则是在关闭当前会话的时候清除数据。只要当前会话没有关闭，即使事务完成了，会话临时表中的数据仍然存在，不会被清除。

（三）临时表的统计信息

临时表默认是不收集统计信息的，但是可以使用DBMS_STATS.GATHER_SCHEMA_STATS在SCHEMA级别收集，需要设置GATHER_TEMP为TRUE（默认为FALSE）；也可以和普通表一样使用GATHER_TABLE_STATS在表级别来收集。需要注意的是，只能收集会话临时表的统计信息，不能收集事务临时表的统计信息。会话临时表的统计信息是被所有的会话所共享的，所以，在一般情况下，不建议收集临时表的统计信息，否则可能会导致很严重的数据库性能问题。临时表的统计信息在生成执行计划时一般是被动态采样的。

下面给出一个示例，该示例演示了由于收集了临时表的统计信息引发的性能问题。

环境准备：

DROP TABLE T_20170619_LHR CASCADE CONSTRAINTS PURGE;

DROP TABLE T_CGTT_20170619_LHR CASCADE CONSTRAINTS PURGE;

CREATE TABLE T_20170619_LHR (

ID NUMBER NOT NULL,

N NUMBER,

CONTENTS VARCHAR2(4000)

);

CREATE GLOBAL TEMPORARY TABLE T_CGTT_20170619_LHR (

ID NUMBER NOT NULL,

T_ID NUMBER NOT NULL,

N NUMBER,

CONTENTS VARCHAR2(4000)

) ON COMMIT PRESERVE ROWS

;

EXECUTE DBMS_RANDOM.SEED(0);

INSERT INTO T_20170619_LHR

SELECT ROWNUM, ROWNUM, DBMS_RANDOM.STRING('A', 50)

FROM DUAL

CONNECT BY LEVEL <= 10

ORDER BY DBMS_RANDOM.RANDOM;

INSERT INTO T_CGTT_20170619_LHR

SELECT ROWNUM, ROWNUM, ROWNUM, DBMS_RANDOM.STRING('B', 50)

FROM DUAL CONNECT BY LEVEL <= 100000

ORDER BY DBMS_RANDOM.RANDOM;

COMMIT;

SELECT COUNT(*) FROM T_20170619_LHR; --10

SELECT COUNT(*) FROM T_CGTT_20170619_LHR; --100000

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'LHR',TABNAME => 'T_20170619_LHR',ESTIMATE_PERCENT => 100,CASCADE=>TRUE);

正常的执行计划如下：

SET LINESIZE 1000

ALTER SESSION SET STATISTICS_LEVEL=ALL ;

SELECT *

FROM T_20170619_LHR A,T_CGTT_20170619_LHR B

WHERE A.ID = B.T_ID;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST ADVANCED'));

PLAN_TABLE_OUTPUT

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

SQL_ID awa3ys5000qc1, child number 0

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

SELECT * FROM T_20170619_LHR A,T_CGTT_20170619_LHR B WHERE A.ID = B.T_ID

Plan hash value: 1110746760

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

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

| 0 | SELECT STATEMENT | | 1 | | | 272 (100)| | 10 |00:00:00.03 | 990 | | | |

|* 1 | HASH JOIN | | 1 | 10 | 20980 | 272 (1)| 00:00:04 | 10 |00:00:00.03 | 990 | 821K| 821K| 1142K (0)|

| 2 | TABLE ACCESS FULL| T_20170619_LHR | 1 | 10 | 570 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 6 | | | |

| 3 | TABLE ACCESS FULL| T_CGTT_20170619_LHR | 1 | 90611 | 176M| 268 (1)| 00:00:04 | 100K|00:00:00.01 | 984 | | | |

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1

2 - SEL$1 / A@SEL$1

3 - SEL$1 / B@SEL$1

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

FULL(@"SEL$1" "A"@"SEL$1")

FULL(@"SEL$1" "B"@"SEL$1")

LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")

USE_HASH(@"SEL$1" "B"@"SEL$1")

END_OUTLINE_DATA

Predicate Information (identified by operation id):

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

1 - access("A"."ID"="B"."T_ID")

Column Projection Information (identified by operation id):

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

1 - (#keys=1) "A"."ID"[NUMBER,22], "B"."T_ID"[NUMBER,22], "A"."CONTENTS"[VARCHAR2,4000], "A"."N"[NUMBER,22], "B"."ID"[NUMBER,22],

"B"."CONTENTS"[VARCHAR2,4000], "B"."N"[NUMBER,22]

2 - "A"."ID"[NUMBER,22], "A"."N"[NUMBER,22], "A"."CONTENTS"[VARCHAR2,4000]

3 - "B"."ID"[NUMBER,22], "B"."T_ID"[NUMBER,22], "B"."N"[NUMBER,22], "B"."CONTENTS"[VARCHAR2,4000]

Note

-----

- dynamic sampling used for this statement (level=2)

这个时候执行计划是正确的，因为T_20170619_LHR是小表（共10行数据），而T_CGTT_20170619_LHR是大表（共100000行数据），处于被驱动的位置，是正确的，而且在执行中，Oracle对大表使用了动态采样。下面新开一个会话，然后收集全局临时表T_CGTT_20170619_LHR表的统计信息，如下：

SYS@orclasm > SELECT TABLE_NAME,

2 PARTITION_NAME,

3 LAST_ANALYZED,

4 PARTITION_POSITION,

5 NUM_ROWS

6 FROM DBA_TAB_STATISTICS T

7 WHERE TABLE_NAME = 'T_CGTT_20170619_LHR';

TABLE_NAME PARTITION_NAME LAST_ANALYZED PARTITION_POSITION NUM_ROWS

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

T_CGTT_20170619_LHR

SYS@orclasm > EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'LHR',TABNAME => 'T_CGTT_20170619_LHR',ESTIMATE_PERCENT => 100,CASCADE=>TRUE,NO_INVALIDATE => FALSE); --注意这里要加NO_INVALIDATE => FALSE

PL/SQL procedure successfully completed.

SYS@orclasm > SELECT TABLE_NAME,

2 PARTITION_NAME,

3 LAST_ANALYZED,

4 PARTITION_POSITION,

5 NUM_ROWS

6 FROM DBA_TAB_STATISTICS T

7 WHERE TABLE_NAME = 'T_CGTT_20170619_LHR';

TABLE_NAME PARTITION_NAME LAST_ANALYZED PARTITION_POSITION NUM_ROWS

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

T_CGTT_20170619_LHR 2017-06-19 15:30:24 0

然后回到刚才的会话，继续查询执行计划，发现执行计划变动了：

SET LINESIZE 1000

ALTER SESSION SET STATISTICS_LEVEL=ALL ;

SELECT *

FROM T_20170619_LHR A,T_CGTT_20170619_LHR B

WHERE A.ID = B.T_ID;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST ADVANCED'));

PLAN_TABLE_OUTPUT

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

SQL_ID awa3ys5000qc1, child number 0

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

SELECT * FROM T_20170619_LHR A,T_CGTT_20170619_LHR B WHERE A.ID = B.T_ID

Plan hash value: 991471220

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

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

| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 10 |00:00:00.05 | 989 | | | |

|* 1 | HASH JOIN | | 1 | 1 | 2098 | 6 (17)| 00:00:01 | 10 |00:00:00.05 | 989 | 10M| 2143K| 11M (0)|

| 2 | TABLE ACCESS FULL| T_CGTT_20170619_LHR | 1 | 1 | 2041 | 2 (0)| 00:00:01 | 100K|00:00:00.01 | 982 | | | |

| 3 | TABLE ACCESS FULL| T_20170619_LHR | 1 | 10 | 570 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 7 | | | |

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1

2 - SEL$1 / B@SEL$1

3 - SEL$1 / A@SEL$1

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

FULL(@"SEL$1" "B"@"SEL$1")

FULL(@"SEL$1" "A"@"SEL$1")

LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")

USE_HASH(@"SEL$1" "A"@"SEL$1")

END_OUTLINE_DATA

Predicate Information (identified by operation id):

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

1 - access("A"."ID"="B"."T_ID")

Column Projection Information (identified by operation id):

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

1 - (#keys=1) "B"."T_ID"[NUMBER,22], "A"."ID"[NUMBER,22], "B"."ID"[NUMBER,22], "B"."CONTENTS"[VARCHAR2,4000], "B"."N"[NUMBER,22],

"A"."CONTENTS"[VARCHAR2,4000], "A"."N"[NUMBER,22]

2 - "B"."ID"[NUMBER,22], "B"."T_ID"[NUMBER,22], "B"."N"[NUMBER,22], "B"."CONTENTS"[VARCHAR2,4000]

3 - "A"."ID"[NUMBER,22], "A"."N"[NUMBER,22], "A"."CONTENTS"[VARCHAR2,4000]

可见，没有使用动态采样，而且生成的执行计划是错误的，因为大表（10W条记录）竟然做了驱动表。当删除临时表的统计信息后，执行计划恢复正常：

LHR@orclasm > EXEC DBMS_STATS.DELETE_TABLE_STATS(OWNNAME => 'LHR',TABNAME => 'T_CGTT_20170619_LHR',NO_INVALIDATE => FALSE);