oracle临时表

临时表概念:

    临时表就是用来暂时保存临时数据(亦或叫中间数据)的一个数据库对象,它和普通表有些类似,然而又有很大区别。它只能存储在临时表空间,而非用户的表空间。ORACLE临时表是会话或事务级别的,只对当前会话或事务可见。每个会话只能查看和修改自己的数据。

用途: 

什么时候使用临时表?用临时表和用中间表有啥区别呢?

我觉得是在需要的时候应用,下面是David Dai关于临时表的一个应用说明,我觉得非常形象的说明了临时表的应用场景:对于一个电子商务类网站,不同消费者在网站上购物,就是一个独立的 SESSION,选购商品放进购物车中,最后将购物车中的商品进行结算。也就是说,必须在整个SESSION期间保存购物车中的信息。同时,还存在有些消费者,往往最终结账时放弃购买商品。如果,直接将消费者选购信息存放在最终表(PERMANENT)中,必然对最终表造成非常大的压力。因此,对于这种案例,就可以采用创建临时表(ON COMMIT PRESERVE ROWS)的方法来解决。数据只在 SESSION 期间有效,对于结算成功的有效数据,转移到最终表中后,ORACLE自动TRUNCATE 临时数据;对于放弃结算的数据,ORACLE 同样自动进行 TRUNCATE ,而无须编码控制,并且最终表只处理有效订单,减轻了频繁的DML操作的压力。

1:当处理某一批临时数据,需要多次DML操作时(插入、更新等),建议使用临时表。

2:当某些表在查询里面,需要多次用来做连接时。(为了获取目标数据需要关联A、B、C, 同时为了获取另外一个目标数据,需要关联D、B、C....)

关于临时表和中间表(NOLOGGING,保存中间数据,使用完后删除)那个更适合用来存储中间数据,我个人更倾向于使用临时表,而不建议使用中间表。

 

临时表又分为会话临时表和事务临时表

 

会话临时表:  会话级的临时表的数据和你当前会话有关系,当前SESSION不退出的情况下,临时表中的数据就还存在,临时表的数据只有当你退出当前SESSION的时候才被截断(TRUNCATE TABLE),如下所示:

        会话级别的临时表创建:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
CREATE GLOBAL TEMPORARY TABLE TMP_TEST
 
(
 
    ID NUMBER ,
 
    NAME VARCHAR2(32)
 
) ON COMMIT PRESERVE ROWS;
 
 
CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT PRESERVE ROWS
 
AS
 
SELECT * FROM TEST;
 
操作示例:
 
SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST
 
 (
 
   ID NUMBER ,
 
   NAME VARCHAR2(32)
 
 ) ON COMMIT PRESERVE ROWS;
 
Table created
 
SQL> INSERT INTO TMP_TEST
 
    SELECT 1, 'kerry' FROM DUAL;
 
1 row inserted
 
SQL> COMMIT;
 
Commit complete
 
SQL> SELECT * FROM TMP_TEST;
 
ID         NAME
 
---------- ----------------
 
1         kerry
 
SQL> INSERT INTO TMP_TEST
 
   SELECT 2, 'rouce' FROM DUAL;
 
1 row inserted
 
SQL> ROLLBACK;
 
Rollback complete
 
SQL> SELECT * FROM TMP_TEST;
 
ID           NAME
 
---------- ----------------------
 
1           kerry
 
SQL>

  

事务临时表:      这种类型的临时表与事务有关,当进行事务提交或者事务回滚的时候,临时表的数据将自行截断,即当COMMIT或ROLLBACK时,数据就会被TRUNCATE掉,其它的特性和会话级的临时表一致。

    

  事务级临时表的创建方法:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
CREATE GLOBAL TEMPORARY TABLE TMP_TEST
 
(
 
    ID NUMBER ,
 
    NAME VARCHAR2(32)
 
) ON COMMIT DELETE ROWS;
 
 
CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT DELETE AS SELECT * FROM TEST;
 
SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST
(
     ID NUMBER ,
     NAME VARCHAR2(32)
 
 ) ON COMMIT DELETE ROWS;
 
Table created
 
SQL> INSERT INTO TMP_TEST
 
   SELECT 1, 'kerry' FROM DUAL;
 
1 row inserted
 
SQL> SELECT * FROM TMP_TEST;
 
ID           NAME
 
---------- ----------------------
 
1           kerry
 
SQL> COMMIT;
 
Commit complete
 
SQL> SELECT * FROM TMP_TEST;
 
ID             NAME
 
---------- ------------------------
 
SQL>

  

  关于临时表只对当前会话或事务可见。每个会话只能查看和修改自己的数据。

  用DM用户登录数据库,打开SESSION 1后,创建临时表TMP_TEST

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
CREATE GLOBAL TEMPORARY TABLE TMP_TEST
 
(
 
    ID NUMBER ,
 
    NAME VARCHAR2(32)
 
) ON COMMIT DELETE ROWS;
 
 
CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT DELETE AS SELECT * FROM TEST;
 
SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST
 
 (
 
     ID NUMBER ,
 
     NAME VARCHAR2(32)
 
 ) ON COMMIT DELETE ROWS;
 
Table created
 
SQL> INSERT INTO TMP_TEST
 
   SELECT 1, 'kerry' FROM DUAL;
row inserted
 
SQL> SELECT * FROM TMP_TEST;
 
ID           NAME
 
---------- ---------------------
kerry
 
SQL> COMMIT;
 
Commit complete
 
SQL> SELECT * FROM TMP_TEST;
 
ID           NAME
 
---------- -----------------------
 
SQL>

  

用sys用户登录数据库,打开SESSION 2

SELECT * FROM DBA_TABLES WHERE TABLE_NAME='TMP_TEST' --可以查到临时表数据

SELECT * FROM DM.TMP_TEST; --查不到数据,即使TMP_TEST临时表存在数据。

 

临时表与永久表区别

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> SELECT TABLE_NAME, TABLESPACE_NAME,"LOGGING",
 
    "TEMPORARY", DURATION, "MONITORING"
 
   FROM DBA_TABLES WHERE TABLE_NAME IN ('TMP_TEST', 'TEST') ;
 
TABLE_NAME   TABLESPACE_NAME LOGGING TEMPORARY DURATION    MONITORING
 
------------- --------------  ------- --------- ----------- ---------
 
TEST          TBS_EDS_DATA    YES       N                      YES
 
TMP_TEST                      NO        Y     SYS$SESSION       NO

  

如上所示,临时表是存储在临时表空间里面的,但是上面脚本可以看出,临时表在数据字典中没有指定其表空间,临时表是NOLOGGING,DURATION为SYS$SESSION

临时表的DML操作速度比较快,但同样也是要产生 Redo Log ,只是同样的DML语句,比对 PERMANENT 的DML 产生的Redo Log 少其实在应用中,往往会创建一个NOLOGGING的永久表(中间表)来保存中间数据,从而代替临时表

posted @   关键我是你浩哥  阅读(525)  评论(0编辑  收藏  举报
编辑推荐:
· .NET Core内存结构体系(Windows环境)底层原理浅谈
· C# 深度学习:对抗生成网络(GAN)训练头像生成模型
· .NET 适配 HarmonyOS 进展
· .NET 进程 stackoverflow异常后,还可以接收 TCP 连接请求吗?
· SQL Server统计信息更新会被阻塞或引起会话阻塞吗?
阅读排行:
· 传国玉玺易主,ai.com竟然跳转到国产AI
· 本地部署 DeepSeek:小白也能轻松搞定!
· 自己如何在本地电脑从零搭建DeepSeek!手把手教学,快来看看! (建议收藏)
· 我们是如何解决abp身上的几个痛点
· 普通人也能轻松掌握的20个DeepSeek高频提示词(2025版)
点击右上角即可分享
微信分享提示