详解Oracle数据库中的会话临时表、事务临时表、中间表区分

概述

今天主要总结下Oracle数据库中的会话临时表、事务临时表、中间表怎么区分,先介绍各自概念。

在Oracle数据库中还有一种类型的表,叫做临时表。这个临时表跟永久表最大的区别就是表中的数据不会永远的存在。当一个会话结束或者事务结束的时候,这个临时表中的数据,不用用户自己删除,数据库自己会自动清除。


临时表的应用场景

1.大表分割

众所周知,为表创建合适的索引可以在很大程度上提高数据查询的速度。但是当某个表的数据量相当大,例如数据量为亿级时,那么创建索引将会花费大量的时间,而且,查询大的索引表,与直接查询数据表相比,在性能上几乎没有任何优势,此时,一个常用的方法就是分割大表,例如:将大表分割为多个小的临时表,然后对这些小表进行相应操作,最后对所有查询结果进行综合处理。

详解Oracle数据库中的会话临时表、事务临时表、中间表区分

2.解决并行问题

当多个进程同时对某张表进行操作时,往往会出现并行问题。

3.作为数据缓存

在程序段,可能需要对若干数据进行复杂运算。此时,可以创建一个临时表,并将这些数据存储在临时表中。因为可以像操作普通表一样操作临时表,这样,许多函数和sql语句都可以用来处理这些数据。


会话级的临时表

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

语法如下:

CREATE GLOBAL TEMPORARY ( ) ON COMMIT PRESERVE ROWS;

--ON COMMIT PRESERVE ROWS 说明临时表是会话指定,当中断会话时ORACLE将截断表。


事务级的临时表

事务临时表的话,当事务结束的时候,就会清空这个事务临时表。所以,当我们在数据库临时表中插入数据后,只要事务没有提交的话,该表中的数据就会存在。但是,当事务提交以后,该表中的数据就会被删除。而且,这个变化不会在重做日志中显示。

语法如下:

CREATE GLOBAL TEMPORARY ( ) ON COMMIT DELETE ROWS;

--ON COMMIT DELETE ROWS 说明临时表是事务指定,每次提交后ORACLE将截断表(删除全部行)

详解Oracle数据库中的会话临时表、事务临时表、中间表区分


中间表

中间表是业务逻辑中的概念,就是将计算结果先保存在一个临时的表中,然后再从这个表中计算,减少程序的复杂度。临时表是中间表多采用的一种技术,使用 Oracle临时表功能可以免去中间表数据的维护工作。


命令区分

SELECT TABLE_NAME, TEMPORARY, DURATION FROM USER_TABLES WHERE TABLE_NAME LIKE 'T_T%';

详解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,保存中间数据,使用完后删除)那个更适合用来存储中间数据,我个人更倾向于使用临时表,而不建议使用中间表。

一般对于复杂的sql语句处理,需要中间表作为临时数据存储。这里可以有2种选择,临时表(global temporary table)和实体表(即普通表).对于数据产生的undo而言,临时表和实体表产生的undo量差不多。但是对于redo,insert产生的redo量很少,相当于实体表的5%,可以认为很少。update产生的redo约为实体表的一般。如此,在不需要session间交换数据的情况下,应该建议使用临时表,而摒弃使用普通表来做数据中转。


总之,如果在不需要session间交换数据的情况下,应该建议使用临时表,如果需要多个线程(多个会话)交换数据就需要使用中间表。

后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注一下~

详解Oracle数据库中的会话临时表、事务临时表、中间表区分

相关推荐