ORACLE临时表的应用-数据库-IT落伍者

前言

目前所有使用Oracle作为数据库支撑平台的应用大部分数据量比较庞大的系统即表的数据量一般情况下都是在百万级以上的数据量当然在Oracle 中创建分区是一种不错的选择但是当你发现你的应用有多张表关联的时候并且这些表大部分都是比较庞大而你关联的时候发现其中的某一张或者某几张表关联之后得到的结果集非常小并且查询得到这个结果集的速度非常快那么这个时候我考虑在Oracle中创建临时表

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

临时表的创建

创建Oracle临时表可以有两种类型的临时表会话级的临时表和事务级的临时表

）会话级的临时表因为这这个临时表中的数据和你的当前会话有关系当你当前SESSION不退出的情况下临时表中的数据就还存在而当你退出当前 SESSION的时候临时表中的数据就全部没有了当然这个时候你如果以另外一个SESSION登陆的时候是看不到另外一个SESSION中插入到临时表中的数据的即两个不同的SESSION所插入的数据是互不相干的当某一个SESSION退出之后临时表中的数据就被截断（truncate table即数据清空）了会话级的临时表创建方法Create Global Temporary Table Table_Name（Col TypeCol Type……） On Commit Preserve Rows举例create global temporary table Student（Stu_id Number（）Class_id Number（）Stu_Name Varchar（）Stu_Memo varchar（）） on Commit Preserve Rows

）事务级临时表是指该临时表与事务相关当进行事务提交或者事务回滚的时候临时表中的数据将自行被截断其他的内容和会话级的临时表的一致（包括退出 SESSION的时候事务级的临时表也会被自动截断）事务级临时表的创建方法Create Global Temporary Table Table_Name（Col TypeCol Type……） On Commit Delete Rows举例create global temporary table Classes（Class_id Number（）Class_Name Varchar（）Class_Memo varchar（）） on Commit delete Rows

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

例子

）会话级（Session关闭掉之后数据就没有了当Commit的时候则数据还在当Rollback的时候则数据也是一样被回滚）

insert into student(stu_id<fp class='fp-tqsh0'></fp>class_id<fp class='fp-tqsh0'></fp>stu_name<fp class='fp-sqkcc'></fp>stu_memo) values(<fp class='fp-34cnd'></fp><fp class='fp-tqsh0'></fp><fp class='fp-x9mhj'></fp><fp class='fp-sqkcc'></fp><fp class='fp-r74kb'></fp><fp class='fp-r74kb'></fp>张三<fp class='fp-4stpj'></fp><fp class='fp-wl5fd'></fp><fp class='fp-7aprw'></fp><fp class='fp-4stpj'></fp><fp class='fp-4stpj'></fp>福建<fp class='fp-r74kb'></fp><fp class='fp-wl5fd'></fp>);insert into student(stu_id<fp class='fp-sesat'></fp>class_id<fp class='fp-sesat'></fp>stu_name<fp class='fp-7aprw'></fp>stu_memo) values(<fp class='fp-l0mcy'></fp><fp class='fp-sqkcc'></fp><fp class='fp-x9mhj'></fp><fp class='fp-sqkcc'></fp><fp class='fp-wl5fd'></fp><fp class='fp-4stpj'></fp>刘德华<fp class='fp-r74kb'></fp><fp class='fp-ctqx3'></fp><fp class='fp-sqkcc'></fp><fp class='fp-wl5fd'></fp><fp class='fp-ctqx3'></fp>福州<fp class='fp-ctqx3'></fp><fp class='fp-ctqx3'></fp>);insert into student(stu_id<fp class='fp-tqsh0'></fp>class_id<fp class='fp-tqsh0'></fp>stu_name<fp class='fp-7aprw'></fp>stu_memo) values(<fp class='fp-kc69s'></fp><fp class='fp-sesat'></fp><fp class='fp-3f4pw'></fp><fp class='fp-sqkcc'></fp><fp class='fp-wl5fd'></fp><fp class='fp-wl5fd'></fp>S<fp class='fp-gocz4'></fp>H<fp class='fp-rqc8n'></fp>E<fp class='fp-4stpj'></fp><fp class='fp-wl5fd'></fp><fp class='fp-7aprw'></fp><fp class='fp-wl5fd'></fp><fp class='fp-4stpj'></fp>厦门<fp class='fp-r74kb'></fp><fp class='fp-4stpj'></fp>);SQL> select *from student ;

STU_ID CLASS_ID STU_NAME STU_MEMO 张三福建刘德华福州 SHE 厦门张惠妹厦门

SQL> commit;Commit completeSQL> select * from student ;

STU_ID CLASS_ID STU_NAME STU_MEMO 张三福建刘德华福州 SHE 厦门张惠妹厦门

SQL>insert into student(stu_id<fp class='fp-sqkcc'></fp>class_id<fp class='fp-tqsh0'></fp>stu_name<fp class='fp-tqsh0'></fp>stu_memo) values(<fp class='fp-t29dz'></fp><fp class='fp-7aprw'></fp><fp class='fp-3f4pw'></fp><fp class='fp-tqsh0'></fp><fp class='fp-4stpj'></fp><fp class='fp-r74kb'></fp>张惠妹<fp class='fp-r74kb'></fp><fp class='fp-wl5fd'></fp><fp class='fp-sesat'></fp><fp class='fp-ctqx3'></fp><fp class='fp-wl5fd'></fp>厦门<fp class='fp-wl5fd'></fp><fp class='fp-wl5fd'></fp>);<fp class='fp-34cnd'></fp> row insertedSQL> select * from student ;

STU_ID CLASS_ID STU_NAME STU_MEMO 张三福建刘德华福州 SHE 厦门张惠妹厦门张惠妹厦门

SQL> rollback ;Rollback completeSQL> select * from student ;

STU_ID CLASS_ID STU_NAME STU_MEMO 张三福建刘德华福州 SHE 厦门张惠妹厦门 SQL>

）事务级（Commit之后就删除数据）本例子将采用以下的数据

insert into classes(Class_idClass_NameClass_Memo) values(计算机);insert into classes(Class_idClass_NameClass_Memo) values(经济信息); insert into classes(Class_idClass_NameClass_Memo) values(经济信息);

在一个SESSION中（比如SQLPLUS登陆）插入上面条记录然后再以另外一个SESSION（用SQLPLUS再登陆一次）登陆当你 select * from classes的时候classes表是空的而你再第一次登陆的SQLPLUS中select的时候可以查询到这个时候你没有进行commit或者rollback之前你可以对刚才插入的条记录进行updatedelete等操作当你进行commit或者rollback的时候这个时候由于你的表是事务级的临时表那么在插入数据的session也看不到数据了这个时候数据就已经被截断了

运行结果如下

SQL> insert into classes(Class_id<fp class='fp-sqkcc'></fp>Class_Name<fp class='fp-tqsh0'></fp>Class_Memo) values(<fp class='fp-x9mhj'></fp><fp class='fp-sqkcc'></fp><fp class='fp-wl5fd'></fp><fp class='fp-r74kb'></fp>计算机<fp class='fp-4stpj'></fp><fp class='fp-4stpj'></fp><fp class='fp-7aprw'></fp><fp class='fp-wl5fd'></fp><fp class='fp-ctqx3'></fp><fp class='fp-bmr02'></fp><fp class='fp-te1fi'></fp><fp class='fp-v1pmc'></fp><fp class='fp-g5t59'></fp><fp class='fp-4stpj'></fp><fp class='fp-ctqx3'></fp>);<fp class='fp-zljnv'></fp> row insertedSQL> insert into classes(Class_id<fp class='fp-sesat'></fp>Class_Name<fp class='fp-7aprw'></fp>Class_Memo) values(<fp class='fp-3f4pw'></fp><fp class='fp-sqkcc'></fp><fp class='fp-ctqx3'></fp><fp class='fp-wl5fd'></fp>经济信息<fp class='fp-r74kb'></fp><fp class='fp-ctqx3'></fp><fp class='fp-sqkcc'></fp><fp class='fp-4stpj'></fp><fp class='fp-wl5fd'></fp><fp class='fp-jcx54'></fp><fp class='fp-9mmx8'></fp><fp class='fp-o7rct'></fp><fp class='fp-8y5u2'></fp><fp class='fp-4stpj'></fp><fp class='fp-4stpj'></fp>);<fp class='fp-k7d4b'></fp> row insertedSQL> insert into classes(Class_id<fp class='fp-tqsh0'></fp>Class_Name<fp class='fp-sesat'></fp>Class_Memo) values(<fp class='fp-0x7ni'></fp><fp class='fp-tqsh0'></fp><fp class='fp-wl5fd'></fp><fp class='fp-r74kb'></fp>经济信息<fp class='fp-wl5fd'></fp><fp class='fp-ctqx3'></fp><fp class='fp-sqkcc'></fp><fp class='fp-r74kb'></fp><fp class='fp-wl5fd'></fp><fp class='fp-mo3f4'></fp><fp class='fp-te1fi'></fp><fp class='fp-aa2ze'></fp><fp class='fp-h51ax'></fp><fp class='fp-4stpj'></fp><fp class='fp-r74kb'></fp>);<fp class='fp-x9mhj'></fp> row insertedSQL> update classes set class_memo =<fp class='fp-4stpj'></fp><fp class='fp-r74kb'></fp><fp class='fp-4stpj'></fp><fp class='fp-wl5fd'></fp> where class_id=<fp class='fp-h51ax'></fp> ;<fp class='fp-x9mhj'></fp> row updatedSQL> select * from classes ;

CLASS_ID CLASS_NAME CLASS_MEMO 计算机经济信息经济信息SQL> delete from classes where class_id= ; row deletedSQL> select * from classes ;CLASS_ID CLASS_NAME CLASS_MEMO 计算机经济信息 SQL> commit;Commit completeSQL> select *from classes ;CLASS_ID CLASS_NAME CLASS_MEMO SQL>再重复插入一次然后rollbackSQL> Rollback ;Rollback completeSQL> select * from classes ;CLASS_ID CLASS_NAME CLASS_MEMO SQL>

临时表的应用

）当某一个SQL语句关联的表在张及以上并且和一些小表关联可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中

）程序执行过程中可能需要存放一些临时的数据这些数据在整个程序的会话过程中都需要用的等等

注意事项

）临时表的索引以及对表的修改删除等和正常的表是一致的

）Oracle的临时表是Oraclei才支持的功能特性如果你的Oracle版本比较低的话那么就可能没有办法用到了如果你的Oracle 版本是i的话你还需要把$ORACLE_HOME/admin/${ORACLE_SID}/pfile目录下的init< ORACLE_SID>ora初始参数配置文件的compatible修改为compatible = 我的服务器上就是这样子配置的当然也可以修改为compatible =

以上是我在对大表进行优化的时候采用的一些手段效果显着