Oracle 临时事务表 全局临时表_global temporary table

简介: 所有的操作都在一个事务里,事务提交后,此表清空,特别适合做插入删除频率特别高的临时表操作,比如插入完数据就开始查询,查询完就删掉等,用完就扔! 临时表分事务级临时表和会话级临时表。 事务级临时表只对当前事务有效,通过语句:ON COMMIT DELETE ROWS 指定。

所有的操作都在一个事务里,事务提交后,此表清空,特别适合做插入删除频率特别高的临时表操作,比如插入完数据就开始查询,查询完就删掉等,用完就扔!

临时表分事务级临时表和会话级临时表。 
事务级临时表只对当前事务有效,通过语句:ON COMMIT DELETE ROWS 指定。 
会话级临时表对当前会话有效,通过语句:ON COMMIT PRESERVE ROWS语句指定。

-- Create table

create global temporary table WFM_TMP_WORKLIST

(

  proc_inst_id NUMBER(10),

  workitem_id  NUMBER(10),

  buzicondi    NVARCHAR2(500)

)

on commit delete rows;


---全局临时表创建语法
SQL> create global temporary table t_global_temp(a int)
  2  on commit delete rows;

Table created.

---查询表名
SQL> select table_name from user_tables where table_name='T_GLOBAL_TEMP';

TABLE_NAME
------------------------------------------------------------
T_GLOBAL_TEMP

--查询表对应的segment
SQL> select segment_name,segment_type from user_segments where segment_name='T_G
LOBAL_TEMP';

no rows selected

---插入数据
SQL> insert into t_global_temp values(1);

1 row created.

SQL> commit;

Commit complete.

--提交查询无记录
SQL> select * from t_global_temp;

no rows selected

--再次查询segment无记录,原因:创建全局临时表指定on commit delete rows一提交即清表
SQL> select segment_name,segment_type from user_segments where segment_name='T_G
LOBAL_TEMP';

no rows selected


SQL> insert into t_global_temp values(1);

1 row created.

--插入不提交即可查询到记录
SQL> select segment_name,segment_type from user_segments where segment_name='T_G
LOBAL_TEMP';

no rows selected

--提交与否皆不占用存储空间,引申问题:哪全局临时表的数据存储在哪儿呢?
SQL> select segment_name,segment_type from user_segments where segment_name='T_G
LOBAL_TEMP';

no rows selected


---以基于会话方式创建全局临时表
SQL> create global temporary table t_global_temp(a int) on commit preserve rows;


Table created.

SQL> insert into t_global_temp values(1);

1 row created.

---提交前查询
SQL> select * from t_global_temp;

         A
----------
         1

SQL> select segment_name,segment_type from user_segments where segment_name='T_G
LOBAL_TEMP';

no rows selected

SQL> commit;

Commit complete.

--提交后查询
SQL> select * from t_global_temp;

         A
----------
         1

---附上提交前后在另一会话查全局临时表测试,全局临时表的数据仅在当前会话可见
SQL> select * from t_global_temp;

no rows selected

SQL> /

no rows selected

SQL> desc t_global_temp;
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------

 A                                                  NUMBER(38)

---测试全局临时表的alter table及create index及alter index
--如全局临时表正在使用alter table不能运行
SQL> alter table t_global_temp add b int;
alter table t_global_temp add b int
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use

---只有退出当前会话
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\123>sqlplus scott/system

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 9 16:07:10 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

---再次alter table即可成功
SQL> alter table t_global_temp add b int;

Table altered.

---在全局临时表构建索引
SQL> create index idx_temp on t_global_temp(a);

Index created.

--删除全局临时表索引
SQL> drop index idx_temp;

Index dropped.

SQL> select count(*) from t_global_temp;

  COUNT(*)
----------
         0

SQL> insert into t_global_temp select 1,3 from dual connect by level<3e5;

299999 rows created.

SQL> commit;

Commit complete.

---收集全局临时表的统计信息
SQL> exec dbms_stats.gather_table_stats(user,'t_global_temp');

PL/SQL procedure successfully completed.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

Plan hash value: 62698482

----------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |   257   (4)| 00:00:04 |
|   1 |  SORT AGGREGATE    |               |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T_GLOBAL_TEMP |   599K|   257   (4)| 00:00:04 |
----------------------------------------------------------------------------

9 rows selected.

---仅插一条a值888888888888888888888的记录到全局临时表
SQL> insert into t_global_temp select 888888888888888888888,1 from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> explain plan for select count(*) from t_global_temp where a=88888888888888
888888;

Explained.

--执行计划显示走了索引
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

Plan hash value: 1743356947

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |          |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_TEMP |     1 |     3 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

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

   2 - access("A"=888888888888888888888)

14 rows selected.

----全局临时表的操作限制

----不能分区,不能集簇化,不能iot化
Temporary tables cannot be partitioned, clustered, or index organized.
 
---不能指定外键约束
You cannot specify any foreign key constraints on temporary tables.
 
---不能包含nested table column
Temporary tables cannot contain columns of nested table.
 
----不能指定lob_storage_clause的参数:tablespace,storage_clause or logging_clause
You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, or logging_clause.
 
---不能启用并行update,delte,merge
Parallel UPDATE, DELETE and MERGE are not supported for temporary tables.
 
---在segment_atrributes_clause子句中,唯一可指定的参数是:tablespace
The only part of the segment_attributes_clause you can specify for a temporary table is TABLESPACE, which allows you to specify a single temporary tablespace.
 
---不支持分布式事务
Distributed transactions are not supported for temporary tables.


小结:全局临时表特别适用于存储中转结果,即临时计算的结果,非最终结果;
     可用于报表统计存储过程.


目录
相关文章
|
存储 SQL Oracle
Oracle数据库批量删除表、视图、序列、存储过程、函数脚本
Oracle数据库批量删除表、视图、序列、存储过程、函数脚本
305 0
|
Oracle 关系型数据库 数据库
Oracle查询优化-复制表的定义及数据
【1月更文挑战第5天】【1月更文挑战第14篇】在Oracle数据库中,复制表定义和复制表数据是两个常见的操作。
237 1
|
3月前
|
存储 Oracle 关系型数据库
Oracle存储过程插入临时表优化与慢查询解决方法
优化是一个循序渐进的过程,就像雕刻一座雕像,需要不断地打磨和细化。所以,耐心一点,一步步试验这些方法,最终你将看到那个让你的临时表插入操作如同行云流水、快如闪电的美丽时刻。
183 14
|
5月前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle的闪回事务查询
Oracle数据库的闪回事务查询(Flashback Transaction Query)是闪回版本查询的扩充,可用于审计或撤销已提交的事务。通过`flashback_transaction_query`视图,可生成还原特定事务的SQL语句。本文介绍了其基本概念,并通过实战演示如何使用该功能:从授权、开启UNDO数据增强,到创建测试表和事务,最后利用闪回查询撤销已提交的事务,验证数据恢复效果。附带视频讲解,帮助深入理解。
135 3
|
消息中间件 关系型数据库 Kafka
实时计算 Flink版产品使用合集之oracle cdc 抽取新增一张表 可以从savepoint恢复吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
119 0
|
存储 Oracle 关系型数据库
Oracle和MySQL有哪些区别?从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
从基本特性、技术选型、字段类型、事务提交方式、SQL语句、分页方法等方面对比Oracle和MySQL的区别。
1993 18
Oracle和MySQL有哪些区别?从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用问题之oracle无主键的表支持同步吗如何实现
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
运维 DataWorks Oracle
DataWorks产品使用合集之在标准模式下,当同步Oracle的表或视图时,是否需要在源端的测试和生产环境中都存在要同步的表或视图
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
129 3
|
资源调度 Oracle 关系型数据库
实时计算 Flink版产品使用合集之同步Oracle数据时,一张表产生了大量的连接数,如何处理
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
SQL Oracle 关系型数据库
【ORACLE】 事务 | 锁 | 约束 | 权限、角色与用户管理
【ORACLE】 事务 | 锁 | 约束 | 权限、角色与用户管理
230 1

推荐镜像

更多