Oracle临时表创建与管理优化

下载需积分: 31 | TXT格式 | 7KB | 更新于2024-11-21 | 197 浏览量 | 举报

Oracle临时表是数据库管理系统Oracle中一种临时存储结构，它在处理大规模数据和复杂查询时扮演着重要角色。当应用需要在运行时临时存储数据，而这些数据不需要长期保留或不会频繁更改时，临时表就显得尤为有用。以下是关于创建和使用临时表的关键知识点： 1. **临时表的创建类型**： - Oracle提供了两种主要类型的临时表：`Global Temporary Table` 和 `Temporary Table`。 - `Global Temporary Table` (GTT) 是一种跨会话的临时表，即使在会话关闭后也会保持数据。有两种模式可供选择：`ON COMMIT PRESERVE ROWS` 和 `ON COMMIT DELETE ROWS`。前者在事务提交时保留数据，后者则在事务提交后删除数据。 - `Temporary Table`（也称为Local Temporary Table）仅存在于单个会话中，会话结束时自动消失，数据不会被其他会话访问。 2. **创建示例**： - 对于`Global Temporary Table`，如`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;`，创建一个会在事务提交时保留行的临时表，适合存储会话间的共享数据。 - 对于`Temporary Table`，如`CREATE TEMPORARY TABLE Classes (Class_id NUMBER(5), Class_Name VARCHAR2(8), Class_Memo VARCHAR2(200)) ON COMMIT DELETE ROWS;`，创建一个会话本地的临时表，数据只在当前会话有效，事务结束时自动清除。 3. **数据操作**： - 在插入数据时，可以使用`INSERT INTO`语句，例如`INSERT INTO Student (stu_id, class_id, stu_name, stu_memo) VALUES (1, 1, '','')`。 - 当需要回滚事务时，`ROLLBACK`将撤销所有对临时表的更改，特别是对于`ON COMMIT DELETE ROWS`类型的表，这可能涉及数据的丢失。 4. **事务管理与数据生命周期**： - Oracle中的临时表操作受事务控制，事务开始（BEGIN）后进行的临时表操作将在事务结束（COMMIT或ROLLBACK）时生效。 - 如果希望在会话结束后保留数据，应使用`ON COMMIT PRESERVE ROWS`，但必须明确在事务结束时手动处理这些临时数据，例如通过显式COMMIT来保存或通过ROLLBACK来清理。总结来说，Oracle中的临时表是提高性能、管理大规模数据和简化查询过程的重要工具。理解并熟练运用不同类型的临时表以及它们的生命周期管理，可以有效地优化数据库应用程序的效率和可维护性。

转)Oracle临时表优化查询速度2008-06-19 17:261、前言

　　目前所有使用Oracle作为数据库支撑平台的应用，大部分数据量比较庞大的系统，即表的数据量一般情况下都是在百万级以上的数据量。

　　当然在Oracle中创建分区是一种不错的选择，但是当你发现你的应用有多张表关联的时候，并且这些表大部分都是比较庞大，而你关联的时候发现其中的某一张或者某几张表关联之后得到的结果集非常小并且查询得到这个结果集的速度非常快，那么这个时候我考虑在Oracle中创建“临时表”。

　　我对临时表的理解：在Oracle中创建一张表，这个表不用于其他的什么功能，主要用于自己的软件系统一些特有功能才用的，而当你用完之后表中的数据就没用了。Oracle的临时表创建之后基本不占用表空间，如果你没有指定临时表（包括临时表的索引）存放的表空的时候，你插入到临时表的数据是存放在ORACLE系统的临时表空间中（TEMP）。

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）、会话级（Session关闭掉之后数据就没有了，当Commit的时候则数据还在，当Rollback的时候则数据也是一样被回滚）：

　　insert into student(stu_id,class_id,stu_name,stu_memo) values(1,1,'张三','福建');
　　insert into student(stu_id,class_id,stu_name,stu_memo) values(2,1,'刘德华','福州');
　　insert into student(stu_id,class_id,stu_name,stu_memo) values(3,2,'S.H.E','厦门');
　　SQL> select *from student ;