看到公司一老员工写的存储过程,跟偶们这些小辈写的对比果然不一样,于是纳闷他那个临时表做什么用,查了下,原来别有洞天.
CREATE OR REPLACE PACKAGE BODY pkg_getsendemail
AS
PROCEDURE getemaillist ( p_rc OUT myrctype)
IS
v_num number;
v_number number;
BEGIN
select count(es_id) into v_num from a
WHERE es_send_flag=0;
if(v_num>0) then
select count(*) into v_number from user_tables where table_name='T_MAILTEMP';
if(v_number<1) then
execute immediate 'CREATE GLOBAL TEMPORARY TABLE T_MAILTEMP ( es_id varchar2(64)) ON COMMIT PRESERVE ROWS';
end if;
execute immediate 'insert into T_MAILTEMP(es_id) select es_id from a WHERE es_send_flag=0 and rownum<=100';
OPEN p_rc FOR
select a.es_id,
a.es_recv_mail,
a.es_title,
a.es_context,
to_char(a.es_send_time,'YYYY-MM-DD HH24:MI:SS') as es_send_time
from
a a, T_MAILTEMP b
where a.es_id=b.es_id ;--and rownum=1;
execute immediate 'update a set es_send_flag=1 WHERE es_id in (select es_id from T_MAILTEMP )';
execute immediate 'delete from T_MAILTEMP ';
else
OPEN p_rc FOR
SELECT '' as es_id ,'' as es_recv_mail,'' as es_title,'' as es_context,'' as es_send_time
from dual;
end if;
END getemaillist;
END pkg_getsendemail;
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"
以上是我在对大表进行优化的时候采用的一些手段,效果显著。
分享到:
相关推荐
本文将深入探讨如何利用Oracle临时表来优化查询速度,并提供具体的实现策略。 #### 一、Oracle临时表的基本概念及应用场景 Oracle临时表是一种特殊类型的表,它们主要用于存储临时数据,这些数据通常在一次会话...
本文将详细介绍Oracle临时表的创建方法、使用场景以及优缺点,帮助读者更好地理解和运用这一特性。 #### 二、Oracle临时表概述 Oracle临时表是一种特殊的表,用于存储临时数据,通常用于事务处理或特定会话期间的...
Oracle 临时表空间不足和批处理缓慢问题探讨 本文探讨了 Oracle 临时表空间不足和批处理缓慢问题的原因和解决方法。通过分析和测试,发现了问题的根源在于应用逻辑方面,具体来说是 SQL 语句的编写问题。通过编写...
理解Oracle临时表的概念及其工作原理对于优化应用程序的性能至关重要。合理使用临时表可以帮助提高数据处理的速度,同时减少不必要的资源消耗。无论是事务临时表还是会话临时表,都有各自的应用场景和限制。在实际...
Oracle 临时表空间是Oracle...总之,理解并正确使用Oracle临时表空间是确保数据库高效运行的关键。通过对临时表空间的合理配置和管理,可以有效地提升数据库的响应速度,降低系统资源消耗,从而优化整体的数据库性能。
Oracle临时表是数据库管理系统...通过学习和理解Oracle临时表,我们可以更有效地管理短暂的、会话级别的数据,优化数据库性能,提升应用的响应速度。在实际工作中,适时使用临时表能为我们的数据库操作带来极大的便利。
通过理解和优化临时表空间,可以显著提升Oracle数据库的性能,尤其是在处理大数据量和复杂查询时。因此,对于数据库管理员来说,了解并掌握这部分知识至关重要,能够帮助他们在日常工作中更有效地管理和维护数据库...
本文重点分析Oracle数据库索引及临时表在查询中的应用,并探讨了基于索引使用SQL语句进行数据库效率优化的几种实现方法。 在Oracle数据库中,索引的合理运用能够显著提升查询速度,减少I/O操作,避免磁盘排序。通常...
#### 二、Oracle临时表概述 临时表是Oracle提供的一种特殊类型的表,用于存储临时数据。它们通常用于执行复杂的计算任务或暂存中间结果,以减少对主表的访问次数,从而提升整体性能。根据生命周期的不同,临时表...
本文将围绕如何提升Oracle数据库的查询统计速度进行深入探讨,并结合实际应用场景给出具体的优化建议。 #### 一、理解Oracle数据库查询统计的基本概念 1. **查询统计**:指的是对数据库中的数据进行筛选、排序、...
4. **子查询和临时表**:子查询可能会导致性能问题,书中的案例可能会演示如何通过重写查询、使用WITH子句(公共表表达式)或者临时表来优化子查询。 5. **分页查询**:在大数据量场景下,高效的分页查询必不可少。...
Oracle查询优化是数据库管理中的重要环节,它直接关系到数据库的性能和响应速度。针对Oracle数据库的查询优化,本文件提供了丰富的技巧和案例分析,以帮助读者深入理解优化原理,并能在实际工作中学以致用。 首先,...
"Oracle查询优化改写"是一个专业领域,旨在通过调整SQL语句的结构和逻辑,提高查询速度,降低资源消耗,从而提升整体系统性能。本文将深入探讨这个主题,结合大量实际案例,提供一系列实用的技巧和方法。 首先,...
4. **减少排序和分组**:尽量避免大表的ORDER BY和GROUP BY操作,如果必须,考虑使用索引或临时表。 三、Oracle数据库特有优化技术 1. **分区表**:通过数据分区,将大表划分为较小、更易管理的部分,提高查询效率...
- **非理想情况:** 如果排序数据超过了排序区的大小限制,则Oracle不得不将这部分数据转移到磁盘上的临时表空间进行排序,这将大大降低性能。 **2.3 调整排序区大小的方法** - **初始化参数SORT_AREA_SIZE:** ...
考虑用JOIN或临时表替换子查询,或使用关联子查询和存在子查询的优化策略。 4. **聚合函数和分组**:使用GROUP BY和HAVING子句时,尽量减少分组列的数量,并考虑预计算或汇总表。对于大量数据,物化视图可以提供...
总结来说,Oracle的临时表是数据库管理中的一种优化策略,特别是在处理复杂查询和大量数据时,通过合理使用临时表,可以优化性能,提高系统的响应速度,同时减少了对数据库物理存储的压力。在设计数据库解决方案时,...
3. **递归查询优化**:当查询涉及多个表关联时,可以通过创建临时表并将其转化为多步操作来提高性能。 #### 三、SQL函数与表关联 - **尽量避免使用复杂的函数**:在WHERE子句中使用函数可能会导致索引失效,从而...
- 子查询可能导致多次表扫描,考虑是否可以转换为连接查询或者使用表变量、临时表来提高性能。 5. **使用绑定变量** - 绑定变量能避免硬解析,提高SQL语句的复用率。尽量避免在SQL语句中使用常量,改为使用绑定...
11. **减少对表的查询**:避免不必要的子查询,尤其是对同一表的重复查询,可以考虑使用JOIN或临时表。 12. **利用内部函数提高效率**:熟练运用Oracle的内置函数,可以简化复杂查询,提高执行速度。 13. **使用表...