Oracle中临时表产生过量Redo的说明

博客围绕Oracle9i中的临时表展开,提出是否使用过临时表以及其是否带来性能提高的疑问,聚焦于信息技术领域数据库相关内容。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

最近,在Oracle9i中你用过临时表吗?

它是否给你带来了性能提高?你注意过么?

好了言归正传.

我们知道临时表在DML操作中可以减少redo的生成,从而在保存中间结果集时可以带来较大的性能提高.

可是,如果你注意到了,在Oracle9i里,临时表可能比常规表还要产生更多的redo:

[oracle@jumper oracle]$ sqlplus eygle/eygle

SQL*Plus: Release 9.2.0.3.0 - Production on Sat Jul 3 16:37:01 2004

Copyright (c) 1982, 2002, Oracle Corporation.? All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production ----请注意版本
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> create table ccc1? (c1 number);

Table created.

SQL> set autotrace trace stat
SQL> insert into ccc1 select rownum from dba_objects;
10468 rows created.


Statistics
----------------------------------------------------------
???????? 73? recursive calls
??????? 175? db block gets
????? 25623? consistent gets
???????? 36? physical reads
???? 157336? redo size --------------------常规表产生的redo,大约154K
??????? 622? bytes sent via SQL*Net to client
??????? 547? bytes received via SQL*Net from client
????????? 3? SQL*Net roundtrips to/from client
????????? 2? sorts (memory)
????????? 0? sorts (disk)
????? 10468? rows processed


SQL> create global temporary table ccc2 (c1 number) ;

Table created.

SQL> set autotrace trace stat
SQL> insert into ccc2 select rownum from dba_objects;

10468 rows created.


Statistics
----------------------------------------------------------
??????? 135? recursive calls
????? 10897? db block gets
????? 25653? consistent gets
??????? 115? physical reads
??? 1476012? redo size ---------------------这是临时表,产生了大约1.4M的redo
??????? 622? bytes sent via SQL*Net to client
??????? 547? bytes received via SQL*Net from client
????????? 3? SQL*Net roundtrips to/from client
????????? 2? sorts (memory)
????????? 0? sorts (disk)
????? 10468? rows processed

这使得在Oracle9i的这些版本中,使用临时表的意义不大了.

?

今天down了个Oracle9205的patch,测试一下在Oracle9205中的情况:

?

E:/Oracle/ora92/bin>sqlplus eygle/eygle 
SQL*Plus: Release 9.2.0.5.0 - Production on 星期六 7月 3 17:37:22 2004 
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 
连接到: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production ---注意版本 
With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.5.0 - Production 
?
SQL> create table ccc1 (c1 number) tablespace eygle; 
表已创建。 
SQL> set autotrace trace stat 
SQL> insert into ccc1 select rownum from dba_objects; 
已创建6188行。 
Statistics 
---------------------------------------------------------- 
50 recursive calls 
157 db block gets 
3416 consistent gets 
0 physical reads 
92764 redo size --------------------------常规表大约产生90K的redo 
611 bytes sent via SQL*Net to client 
547 bytes received via SQL*Net from client 
3 SQL*Net roundtrips to/from client 
2 sorts (memory) 
0 sorts (disk) 
6188 rows processed 
SQL> drop table ccc1; 
表已丢弃。 
SQL> create global temporary table ccc2 (c1 number) ; 
表已创建。 
SQL> set autotrace trace stat 
SQL> insert into ccc2 select rownum from dba_objects; 
已创建6188行。 
Statistics 
---------------------------------------------------------- 
9 recursive calls 
87 db block gets 
3402 consistent gets 
0 physical reads 
16844 redo size -------------------------临时表这时只产生了16K的redo 
614 bytes sent via SQL*Net to client 
547 bytes received via SQL*Net from client 
3 SQL*Net roundtrips to/from client 
2 sorts (memory) 0 sorts (disk) 
6188 rows processed 
SQL>

在9205中Oracle修正了这个bug.

如果你从来没有遇到这个bug,那么你是幸运的:)

?

在buglist中,找到了以下说明:

2874489: Excessive REDO generated for INSERT as SELECT into GLOBAL TEMPORARY TABLES.

Fixed: 9205

?

临时表终于回来了!

?

2004年7月3日 星期六 17:53

If you have any question,please mail to eygle@itpub.net .

?

<think>我们被要求回答关于Oracle SQL临时表的使用方法。根据引用内容,Oracle中有全局临时表(GLOBAL TEMPORARY TABLE)和私有临时表(PRIVATE TEMPORARY TABLE,12c引入)两种。此外,引用[2]还提到了临时表空间的使用情况查询,引用[3]展示了全局临时表的一个例子和undo使用情况。 在Oracle中,临时表用于存储会话或事务中的临时数据,数据在会话结束或事务结束时自动清除。全局临时表的结构是持久的,但数据是临时的。私有临时表(12c及以上)的表结构和数据都是临时的,并在会话结束时自动删除。 下面分别介绍两种临时表的使用方法: 1. 全局临时表(GLOBAL TEMPORARY TABLE): - 创建表时使用`GLOBAL TEMPORARY`关键字。 - 可以指定数据是事务级(ON COMMIT DELETE ROWS)还是会话级(ON COMMIT PRESERVE ROWS)的。 - 事务级:每次提交事务后,数据自动清空。 - 会话级:数据在整个会话期间都存在,直到会话结束或手动删除。 示例: ```sql -- 创建事务级的全局临时表 CREATE GLOBAL TEMPORARY TABLE gtt_transaction ( id NUMBER, name VARCHAR2(50) ) ON COMMIT DELETE ROWS; -- 创建会话级的全局临时表 CREATE GLOBAL TEMPORARY TABLE gtt_session ( id NUMBER, name VARCHAR2(50) ) ON COMMIT PRESERVE ROWS; ``` 2. 私有临时表(PRIVATE TEMPORARY TABLE,12c及以上版本): - 表名必须以`ORA$PTT_`为前缀(可以修改,但通常使用默认前缀)。 - 使用`ON COMMIT`定义数据保留策略,同时表是会话私有的,在会话结束时自动删除(包括结构和数据)。 - 创建语法中使用`PRIVATE TEMPORARY`关键字。 示例: ```sql -- 创建事务级的私有临时表(提交后数据删除,会话结束表自动删除) CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp ( id NUMBER, name VARCHAR2(50) ) ON COMMIT DROP DEFINITION; -- 注意:ON COMMIT DROP DEFINITION 表示提交时删除表(包括结构和数据),但Oracle也支持ON COMMIT PRESERVE DEFINITION(提交时保留表结构,只删除数据) -- 或者使用ON COMMIT PRESERVE DEFINITION(提交后保留表定义,只删除数据,会话结束表自动删除) CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp_session ( id NUMBER, name VARCHAR2(50) ) ON COMMIT PRESERVE DEFINITION; ``` 注意:私有临时表在12.2及以上版本中引入,并且表名必须以前缀开头(默认是ORA$PTT_,但可以通过参数修改)。 使用临时表时,需要注意: - 临时表产生redo日志,但是会产生undo日志(用于回滚),如引用[3]所示。 - 临时表的数据不会备份,也不会被恢复。 - 临时表可以创建索引、约束等,但同样也是临时的。 另外,引用[2]提供了查询临时表空间使用情况的SQL,这对于监控临时表空间的使用很有帮助。 下面给出一个使用全局临时表的完整示例: ```sql -- 创建全局临时表(会话级) CREATE GLOBAL TEMPORARY TABLE my_gtt ( id NUMBER, data VARCHAR2(100) ) ON COMMIT PRESERVE ROWS; -- 插入数据 INSERT INTO my_gtt VALUES (1, 'Session data'); -- 提交后数据仍然存在(因为是会话级) COMMIT; -- 查询数据 SELECT * FROM my_gtt; -- 结束会话后,数据自动清除。 -- 删除全局临时表(如果需要) DROP TABLE my_gtt; ``` 私有临时表的使用示例: ```sql -- 创建私有临时表(事务级:提交时删除表) CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp ( id NUMBER, data VARCHAR2(100) ) ON COMMIT DROP DEFINITION; -- 插入数据 INSERT INTO ora$ptt_my_temp VALUES (1, 'Temporary data'); -- 查询 SELECT * FROM ora$ptt_my_temp; -- 提交后,表自动被删除,无法再查询。 COMMIT; -- 注意:在提交之前,可以像普通表一样使用。 ``` 总结: - 全局临时表:表结构持久,数据临时(根据定义在事务结束或会话结束时清除)。 - 私有临时表:表结构和数据都是临时的,在会话结束或事务提交时(根据定义)自动删除。 根据需求选择合适的临时表类型。 相关问题: 1. 全局临时表和私有临时表的主要区别是什么? 2. 如何监控Oracle临时表空间的使用情况? 3. 临时表在什么情况下会产生重做日志和撤销日志? 4. 在Oracle中,临时表是否支持并行操作?</think>### Oracle SQL 临时表使用方法详解 在Oracle中,临时表主要用于存储会话或事务中的临时数据,分为两种类型:**全局临时表(GLOBAL TEMPORARY TABLE)** 和 **私有临时表(PRIVATE TEMPORARY TABLE)**(12c及以上版本支持)。以下是具体使用方法: --- #### 一、全局临时表(GLOBAL TEMPORARY TABLE) **特点**:表结构持久化存储,数据根据配置在事务或会话结束时自动清除。 1. **创建语法** ```sql CREATE GLOBAL TEMPORARY TABLE 表名 ( 列1 数据类型, 列2 数据类型 ) ON COMMIT [DELETE ROWS | PRESERVE ROWS]; ``` - **`ON COMMIT DELETE ROWS`**:事务级临时表(提交后数据自动清除)[^1][^3] ```sql CREATE GLOBAL TEMPORARY TABLE gtt_transaction ( id INT, name VARCHAR2(50) ) ON COMMIT DELETE ROWS; ``` - **`ON COMMIT PRESERVE ROWS`**:会话级临时表(会话结束前数据保留) ```sql CREATE GLOBAL TEMPORARY TABLE gtt_session ( id INT, value VARCHAR2(10) ) ON COMMIT PRESERVE ROWS; ``` 2. **数据操作** 插入/查询数据与普通表相同,但数据仅对当前会话可见: ```sql INSERT INTO gtt_transaction VALUES (1, '测试'); SELECT * FROM gtt_transaction; -- 仅当前会话可见 ``` 3. **注意事项** - 表结构需提前创建,数据自动清理无需手动干预。 - 不产生重做日志(Redo),但会生成撤销日志(Undo)用于事务回滚[^3]。 --- #### 二、私有临时表(PRIVATE TEMPORARY TABLE, 12c+) **特点**:表结构和数据均为临时,会话结束时自动删除。 1. **创建语法** ```sql CREATE PRIVATE TEMPORARY TABLE 表名 ( 列1 数据类型, 列2 数据类型 ) ON COMMIT [DROP DEFINITION | PRESERVE DEFINITION]; ``` - **`ON COMMIT DROP DEFINITION`**:事务级(提交后表结构和数据均删除) ```sql CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp ( id INT, data VARCHAR2(100) ) ON COMMIT DROP DEFINITION; ``` - **`ON COMMIT PRESERVE DEFINITION`**:会话级(会话结束前保留表结构) ```sql CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp_session ( id INT, value VARCHAR2(20) ) ON COMMIT PRESERVE DEFINITION; ``` 2. **命名规则** 表名必须以 `ORA$PTT_` 开头(默认前缀,可通过参数修改)。 --- #### 三、临时表空间监控 使用以下SQL监控临时表空间使用情况[^2]: ```sql SELECT f.tablespace_name, ROUND(f.sum_space, 2) "总空间(MB)", ROUND(u.used_space, 2) "已用空间(MB)", ROUND(f.sum_space - u.used_space, 2) "空闲空间(MB)", ROUND(u.used_space / f.sum_space * 100, 2) "使用率(%)" FROM ( SELECT tablespace_name, SUM(bytes)/1024/1024 AS sum_space FROM sys.dba_temp_files GROUP BY tablespace_name ) f JOIN ( SELECT tablespace, SUM(blocks * 8)/1024 AS used_space FROM sys.v_$sort_usage GROUP BY tablespace ) u ON f.tablespace_name = u.tablespace; ``` --- #### 四、典型应用场景 1. 中间结果存储(如复杂查询的分步计算)。 2. 批数据处理时暂存中间状态。 3. 避免事务锁竞争(临时表数据对其他会话不可见)。 --- **注意事项**: - 全局临时表需显式创建(DDL操作),私有临时表自动销毁。 - 临时表不支持分区。 - 索引可在临时表上创建,但同样为临时对象。 --- ###
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值