oracle临时表
Oracle 临时表功能介绍:
Oracle中的临时表是全局的,需要在数据库设计时创建完成,而不是程序使用时。每个登陆用户都使用这一个相同的临时表,但互相之间看不到彼此的数据,也就是说临时表是会话独立的。
Oracle 的临时表分为事务型和会话型。
事务型临时表就是指在事务处理过程中插入的记录只在事务提交或回滚之前有效,一旦事务完成,表中的记录便被自动清除。
会话型临时表指临时表中的数据在本次会话期间一直有效,直到会话结束。会话结束后表中针对此次会话的数据会自动清空。
Oracle 临时表的不足之处:
1、不支持lob对象,例如 Spatial 的SDO_GEOMETRY。这也许是设计者基于运行效率的考虑,但实际应用中确实需要此功能时就无法使用临时表了。
2、不支持主外键关系。这意味着临时表
鉴于以上原因,洒家设计了一份自定义的临时表处理办法,使之可以支持Oracle Spatial数据类型和主外键关系,而且不会出现并发冲突。
1、以常规表的形式创建临时数据表的表结构,但要在每一个表的主键中加入一个 SessionID <NUMBER> 列以区分不同的会话。(可以有lob列和主外键)
2、写一个用户注销触发器,在用户结束会话的时候删除本次会话所插入的所有记录(SessionID等于本次会话ID的记录)。
3、程序写入数据时,要顺便将当前的会话ID(SessionID)写入表中。
4、程序读取数据时,只读取与当前会话ID相同的记录即可。
功能增强的扩展设计:
1、可以在数据表上建立一个视图,视图对记录的筛选条件就是当前会话的SessionID。
2、数据表中的SessionID列可以通过Trigger实现,以实现对应用层的透明性。
3、高级用户可以访问全局数据,以实现更加复杂的功能。
扩展临时表的优点:
1、实现了与Oracle的基于会话的临时表相同的功能。
2、支持SDO_GEOMETRY等lob数据类型。
3、支持表间的主外键连接,且主外键连接也是基于会话的。
4、高级用户可以访问全局数据,以实现更加复杂的功能。
最近考虑到我们的数据库端写存储过程关于临时表使用的情况,由于我们现在还不清楚数据库端到底是怎么处理的,是否和Sql Server的处理方式相同,是否会存在隐患等等一些问题,为了避免将来不必要的麻烦我做了深入的研究和查看了一些权威的资料,现在和大家共享,希望大家在处理
1、前言
目前所有使用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 STU_ID CLASS_ID STU_NAME STU_MEMO ------ -------- -------- -------------------------------------------------------------------------------- 1 1 张三 福建 2 1 刘德华 福州 3 2 S.H.E 厦门 4 2 张惠妹 厦门 SQL> commit; Commit complete SQL> select * from student STU_ID CLASS_ID STU_NAME STU_MEMO ------ -------- -------- -------------------------------------------------------------------------------- 1 1 张三 福建 2 1 刘德华 福州 3 2 S.H.E 厦门 4 2 张惠妹 厦门 SQL>insert into student(stu_id,class_id,stu_name,stu_memo) values(4,2,'张惠妹','厦门'); 1 row inserted SQL> select * from student STU_ID CLASS_ID STU_NAME STU_MEMO ------ -------- -------- -------------------------------------------------------------------------------- 1 1 张三 福建 2 1 刘德华 福州 3 2 S.H.E 厦门 4 2 张惠妹 厦门 4 2 张惠妹 厦门 SQL> rollback Rollback complete SQL> select * from student STU_ID CLASS_ID STU_NAME STU_MEMO ------ -------- -------- -------------------------------------------------------------------------------- 1 1 张三 福建 2 1 刘德华 福州 3 2 S.H.E 厦门 4 2 张惠妹 厦门 SQL> 2)、事务级(Commit之后就删除数据):本例子将采用以下的数据: insert into classes(Class_id,Class_Name,Class_Memo) values(1,'计算机','9608'); insert into classes(Class_id,Class_Name,Class_Memo) values(2,'经济信息','9602'); insert into classes(Class_id,Class_Name,Class_Memo) values(3,'经济信息','9603'); 在一个SESSION中(比如SQLPLUS登陆)插入上面3条记录,然后再以另外一个SESSION(用SQLPLUS再登陆一次)登陆,当你select * from classes;的时候,classes表是空的,而你再第一次登陆的SQLPLUS中select的时候可以查询到,这个时候你没有进行commit或者rollback之前你可以对刚才插入的3条记录进行update、delete等操作,当你进行commit或者rollback的时候,这个时候由于你的表是事务级的临时表,那么在插入数据的session也看不到数据了,这个时候数据就已经被截断了。 运行结果如下: SQL> insert into classes(Class_id,Class_Name,Class_Memo) values(1,'计算机','9608'); 1 row inserted SQL> insert into classes(Class_id,Class_Name,Class_Memo) values(2,'经济信息','9602'); 1 row inserted SQL> insert into classes(Class_id,Class_Name,Class_Memo) values(3,'经济信息','9603'); 1 row inserted SQL> update classes set class_memo ='' where class_id=3 1 row updated SQL> select * from classes CLASS_ID CLASS_NAME CLASS_MEMO -------- ---------- -------------------------------------------------------------------------------- 1 计算机 9608 2 经济信息 9602 3 经济信息 SQL> delete from classes where class_id=3 1 row deleted SQL> select * from classes CLASS_ID CLASS_NAME CLASS_MEMO -------- ---------- -------------------------------------------------------------------------------- 1 计算机 9608 2 经济信息 9602 SQL> commit; Commit complete SQL> select *from classes CLASS_ID CLASS_NAME CLASS_MEMO -------- ---------- -------------------------------------------------------------------------------- SQL> 再重复插入一次,然后rollback。 SQL> Rollback Rollback complete SQL> select * from classes CLASS_ID CLASS_NAME CLASS_MEMO -------- ---------- -------------------------------------------------------------------------------- SQL> 4、临时表的应用 1)、当某一个SQL语句关联的表在2张及以上,并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中。 2)、程序执行过程中可能需要存放一些临时的数据,这些数据在整个程序的会话过程中都需要用的等等。 5、注意事项: 1)、临时表的索引以及对表的修改、删除等和正常的表是一致的。 2)、Oracle的临时表是Oracle8i才支持的功能特性,如果你的Oracle版本比较低的话,那么就可能没有办法用到了,如果你的Oracle版本是8i的话,你还需要把$ORACLE_HOME/admin/${ORACLE_SID}/pfile目录下的init<ORACLE_SID>.ora初始参数配置文件的compatible修改为compatible = "8.1.0",我的服务器上就是这样子配置的。当然也可以修改为compatible = "8.1.6" 以上是我在对大表进行优化的时候采用的一些手段,效果显著
文章引用自:
|