oracle临时表

本文深入解析Oracle临时表的两种类型:会话级和事务级,阐述其创建方法、区别及应用场景。重点讨论如何在SQL语句关联多个表时,利用临时表优化查询效率,以及程序执行过程中临时数据的存储与管理。

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

1、临时表

ORACLE临时表分为基于会话和基于事务的两种临时表
临时表上可以创建索引,临时表上的数据操作不会产生重执行信息

ON COMMIT DELETE ROWS---------------默认 基于事务的 提交后数据清空
ON COMMIT preserve rows------------------基于会话的会话结束数据清空

下面举例创建临时表:

create golbal temporary table test(id number) on commit delete rows;

create golbal temporary table test(id number) on commit preserve rows;

2、临时表详细说明

创建oracle临时表,可以有两种类型的临时表:会话级的临时表和事务级的临时表。

  1)会话级的临时表因为这这个临时表中的数据和你的当前会话有关系,当你当前session不退出的情况下,临时表中的数据就还存在,而当你退出当前session的时候,临时表中的数据就全部没有了,当然这个时候你如果以另外一个session登陆的时候是看不到另外一个session中插入到临时表中的数据的。即两个不同的session所插入的数据是互不相干的。当某一个session退出之后临时表中的数据就被截断(truncate table,即数据清空)了。会话级的临时表创建方法:create global temporary table table_name(col1 type1,col2 type2...) on commit preserve rows;举例create global temporary table student(stu_id number(5),class_id  number(5),stu_name varchar2(8),stu_memo varchar2(200)) on commit preserve rows ;

  2)事务级临时表是指该临时表与事务相关,当进行事务提交或者事务回滚的时候,临时表中的数据将自行被截断,其他的内容和会话级的临时表的一致(包括退出session的时候,事务级的临时表也会被自动截断)。事务级临时表的创建方法:create global temporary table table_name(col1 type1,col2 type2...) on commit delete rows;举例:create global temporary table classes(class_id number(5),class_name varchar2(8),class_memo varchar2(200)) on commit delete rows ;

  3)、两种不通类型的临时表的区别:语法上,会话级临时表采用on commit preserve rows而事务级则采用on commit delete rows;用法上,会话级别只有当会话结束临时表中的数据才会被截断,而且事务级临时表则不管是commit、rollback或者是会话结束,临时表中的数据都将被截断。

3、临时表的使用

  1)、当某一个sql语句关联的表在2张及以上,并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中。
  2)、程序执行过程中可能需要存放一些临时的数据,这些数据在整个程序的会话过程中都需要用的等等。

<think>我们被要求解释Oracle临时表的使用和定义。根据用户提供的引用,我们可以总结如下: 引用[1]指出:临时表是用于存储临时数据(中间数据)的数据库对象。它存储在临时表空间中,而不是用户的表空间。Oracle临时表会话事务级别的,只对当前会话或事务可见。每个会话只能查看和修改自己的数据。 引用[2]提到:由于Oracle不是内存数据库,频繁创建和删除临时表会影响性能。因此,Oracle会保留临时表的定义直到用户显式删除(DROP TABLE)。 引用[3]对比了业务表和临时表的区别: - 存储位置:业务表在永久表空间,临时表临时表空间。 - 生命周期:业务表数据持久化,临时表数据在会话结束(会话)或事务提交/回滚(事务级)时自动清除。 - 并发访问:业务表支持多会话并发访问,临时表数据仅对当前会话可见。 - 索引约束:临时表支持主键、唯一约束和普通索引,但不支持外键(通常)。 - 性能:临时表的操作通常不产生重做日志(redo),因此性能更高。 引用[4]展示了全局临时表的undo使用情况,说明插入数据到全局临时表时,产生的redo很少(28528字节,而常规表会产生大量redo),并且undo块使用也较少(4个undo块,55条undo记录)。 根据以上引用,我们可以整理回答如下: ### Oracle临时表的使用和定义 #### 1. **临时表的定义** Oracle临时表(Temporary Table)是一种特殊的数据库对象,用于存储会话或事务期间的临时数据。普通表(业务表)不同,临时表的数据存储在临时表空间中,且数据具有特定的生命周期(会话事务级)。临时表的定义(结构)是持久的,直到被显式删除,但数据是临时的。 #### 2. **临时表的分类** 根据数据清除的时机,Oracle临时表分为两种: - **事务级临时表(ON COMMIT DELETE ROWS)**:数据在事务提交(COMMIT)或回滚(ROLLBACK)时自动清除。 - **会话临时表(ON COMMIT PRESERVE ROWS)**:数据在整个会话期间保留,直到会话结束(断开连接)或显式删除。 #### 3. **创建临时表** 创建临时表的语法如下: ```sql -- 事务级临时表 CREATE GLOBAL TEMPORARY TABLE temp_table_trans ( id NUMBER, name VARCHAR2(50) ) ON COMMIT DELETE ROWS; -- 会话临时表 CREATE GLOBAL TEMPORARY TABLE temp_table_sess ( id NUMBER, name VARCHAR2(50) ) ON COMMIT PRESERVE ROWS; ``` 注意:临时表必须使用`GLOBAL TEMPORARY`关键字。 #### 4. **临时表的特性** - **数据隔离**:每个会话只能看到自己插入的数据,即使多个会话使用同一个临时表名,它们的数据也互不影响[^1]。 - **性能优势**:对临时表的DML操作(INSERT/UPDATE/DELETE)通常不生成重做日志(redo),只生成少量的undo日志,因此性能较高[^4]。 - **索引约束**:临时表可以创建索引(包括主键和唯一约束),但不支持外键约束[^3]。 - **存储空间**:临时表的数据存储在临时表空间中,不会占用永久表空间。 #### 5. **临时表的使用场景** - 存储中间结果集,用于复杂查询的阶段性处理。 - 在批处理过程中暂存数据,避免干扰业务表。 - 需要会话隔离的临时数据存储(如用户登录会话的临时数据)。 #### 6. **使用示例** ```sql -- 创建事务级临时表 CREATE GLOBAL TEMPORARY TABLE gtt_order_summary ( product_id NUMBER, total_sales NUMBER ) ON COMMIT DELETE ROWS; -- 插入数据(当前事务内有效) INSERT INTO gtt_order_summary SELECT product_id, SUM(amount) FROM orders GROUP BY product_id; -- 查询(仅当前事务内可见) SELECT * FROM gtt_order_summary; -- 事务提交后,数据自动清除 COMMIT; ``` #### 7. **注意事项** - **定义持久化**:临时表的定义(结构)会一直存在,直到执行`DROP TABLE`命令[^2]。 - **避免频繁创建/删除**:由于表定义的创建和删除开销较大,建议在应用初始化时创建,长期保留。 - **事务控制**:事务级临时表在事务结束时自动清除数据,因此不要在事务外查询(事务提交后数据消失)。 - **空间管理**:临时表空间需要足够大小,否则可能因空间不足导致操作失败。 ### 总结 Oracle临时表是处理临时数据的有效工具,通过隔离会话数据、减少日志生成来提高性能。根据数据保留需求选择事务级会话临时表,并注意合理管理表定义和临时表空间[^1][^3][^4]。 §§相关问题§§ 1. Oracle临时表和内存表有什么区别? 2. 如何监控临时表空间的使用情况? 3. 临时表在数据仓库ETL过程中的应用有哪些? 4. 临时表WITH子句(CTE)在性能上的差异? 5. 如何为临时表创建索引以优化查询性能?</think>### Oracle临时表的使用定义详解 #### 一、临时表的核心定义 Oracle临时表是专门用于存储**会话事务级别临时数据**的特殊数据库对象,普通业务表有本质区别: 1. **存储位置**:数据仅存储在临时表空间中,而非永久表空间[^1][^3] 2. **数据生命周期**: - 事务级:`ON COMMIT DELETE ROWS` → 事务提交/回滚时自动清除数据 - 会话:`ON COMMIT PRESERVE ROWS` → 会话断开时自动清除数据[^1] 3. **数据可见性**:仅对当前会话可见,不同会话间的数据完全隔离[^1][^3] #### 二、创建语法类型 ```sql -- 事务级临时表(提交后数据消失) CREATE GLOBAL TEMPORARY TABLE temp_transaction ( id NUMBER PRIMARY KEY, data VARCHAR2(100) ) ON COMMIT DELETE ROWS; -- 会话临时表(断开连接后数据消失) CREATE GLOBAL TEMPORARY TABLE temp_session ( id NUMBER, result CLOB ) ON COMMIT PRESERVE ROWS; ``` #### 三、核心特性对比 | 特性 | 业务表 | 临时表 | |---------------------|---------------------|-------------------------| | **数据存储位置** | 永久表空间 | 临时表空间[^3] | | **数据生命周期** | 永久保存 | 会话/事务结束时清除[^1] | | **并发访问** | 多会话共享 | 仅创建会话可见[^3] | | **重做日志(Redo)** | 完整记录 | 基本不生成[^4] | | **索引支持** | 所有索引类型 | 主键/唯一索引/普通索引 | | **外键约束** | 支持 | 通常不支持[^3] | #### 四、典型使用场景 1. **中间结果集处理** ```sql -- 在复杂计算中暂存中间结果 INSERT INTO temp_session SELECT employee_id, SUM(salary) FROM employees GROUP BY department_id; ``` 2. **批量数据预处理** ```sql -- 事务级临时表用于ETL临时存储 INSERT INTO temp_transaction SELECT * FROM source_table WHERE create_date > SYSDATE-1; -- 后续处理(事务提交后自动清空) UPDATE target_table t SET t.value = (SELECT data FROM temp_transaction tmp WHERE tmp.id = t.id); ``` 3. **会话数据缓存** ```sql -- 存储用户会话的临时配置 INSERT INTO temp_session VALUES (1, '{"theme":"dark"}'); -- 同一会话中可多次读取 SELECT result FROM temp_session WHERE id=1; ``` #### 五、性能优势原理 1. **极少的Redo日志** 临时表的DML操作基本不生成重做日志,显著减少I/O开销[^4] ```sql -- 插入万行数据仅产生约28KB redo(常规表需MB) INSERT INTO gtt_test SELECT 1 FROM dual CONNECT BY LEVEL < 10000; -- 统计输出:28528 redo size[^4] ``` 2. **高效的Undo管理** 事务级临时表仅需少量undo块(示例中仅4个undo块处理55条记录)[^4] 3. **空间自动回收** 数据自动清除机制避免手动维护成本[^2] #### 六、最佳实践 1. **定义持久化原则** 创建后保留表定义,避免频繁`CREATE/DROP`(Oracle会保留定义直到显式删除)[^2] 2. **索引使用策略** ```sql -- 在频繁查询的列创建索引 CREATE INDEX idx_temp ON temp_session(id); -- 注意:索引也存储在临时表空间 ``` 3. **CTE的取舍** | 场景 | 推荐方案 | |---------------------|-------------------| | 简单中间结果(<100行) | WITH子句(CTE) | | 大中间结果/多次引用 | 临时表 | | 跨程序单元调用 | 临时表 | 4. **空间监控** ```sql -- 检查临时表空间使用 SELECT tablespace_name, used_blocks FROM v$sort_segment; ``` #### 七、使用限制 1. 不支持外键约束 2. 不能作为永久表的子表 3. 分区功能有限制 4. 不支持在线重定义 > **关键区别总结**:临时表本质是**数据临时性+定义持久性**的组合,特别适合需要会话隔离、减少日志生成的中间数据处理场景[^1][^3][^4]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值