临时表是复杂SQL性能优化中常见手段,总结一下。
1) Global Temporary 句
分为会话级和事务级两种。
- 会话级的临时表
【语法】
Create Global Temporary Table Table_Name
(the aggregation SQL statement) On Commit Preserve Rows;
【特点】
临时表数据自动清空后,但是临时表的结构以及元数据还存储在用户的数据字典中。表的定义对所有的会话可见。
临时表不需要DML锁。
可以索引临时表和在临时表基础上建立视图。
在临时表上的索引也是临时的,也是只对当前会话或者事务有效。
临时表可以拥有触发器。
可以用export和import工具导入导出临时表的定义,但是不能导出数据。
【适用场合】
当某一个SQL语句多表关联,并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中。
程序执行过程中多次使用的临时数据,这些数据在整个程序的会话过程中都需要用的等等。
【清除时点】
当某一个SESSION退出之后自动清除数据(表结构保留)
【不足】
不支持lob对象,这也许是设计者基于运行效率的考虑,但实际应用中确实需要此功能时就无法使用临时表了。
不支持主外键关系。
例子:
CREATE GLOBAL TEMPORARY TABLE my_temporary
(birthdate DATE,
enddate DATE,
name CHAR(20)) ON COMMIT PRESERVE ROWS;
- 事务级的临时表
【语法】
Create Global Temporary Table Table_Name
(the aggregation SQL statement) On Commit Delete Rows;
【清除时点】 当执行COMMIT之后自动清除数据(表结构保留)
其他方面与会话级的临时表相同,不再列出。
2) with句
准确的说with语句不叫临时表,但它与临时表使用概念上非常接近,同样列出。
【语法】
WITH
subquery_name
AS
(the aggregation SQL statement)
SELECT
(query naming subquery_name);
【特点】
附着于一个SQL 语句,也只对一个SQL语句产生作用。
可以同时生成几张临时表。
生存期极短,易于管理。
【适用场合】
Global Temporary 的适用场合都适用,只是针对一个SQL的场合
特别方便融入常量或集合运算结果,有简化SQL的作用。
【清除时点】
查询完成后立即清除(数据及表结构)。
例子:
WITH
sum_sales AS
( select /*+ materialize */
sum(quantity) all_sales from stores ),
number_stores AS
( select /*+ materialize */
count(*) nbr_stores from stores ),
sales_by_store AS
( select /*+ materialize */
store_name, sum(quantity) store_sales from
store natural join sales )
SELECT
store_name
FROM
store,
sum_sales,
number_stores,
sales_by_store
where
store_sales > (all_sales / nbr_stores)
(注:使用/*+ materialize */让Oracle基于cost-based(基于成本)的优化策略生成临时表。 )
相关推荐
很好的,经典.创建Oracle 临时表,可以有两种类型的临时表:会话级的临时表,事务级的临时表 。
临时表的应用 1)、当某一个SQL语句关联的表...可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中。 2)、程序执行过程中可能需要存放一些临时的数据,这些数据在整个程序的会话过程中都需要用的等等。
临时表数据库中临时表的深入研究,原理,用法
系统讲解oracle新建表空间,临时表空间,和用户的方法和使用命令,是难得的技术文档。
当这个分区的大小不足以容纳排序后所产生的记录时,数据库系统就会将临时数据存放到临时表空间中。这就是临时表空间的来历。看起来好像这个临时表空间是个临时工,对于数据库的影响不会有多大。其实大家这是误解这个...
5.6.4 查看临时表在数据库中的信息 86 5.6.5 临时表的应用场景 86 5.7 特殊的表dual 87 5.7.1 分析dual表 87 5.7.2 dual表的应用场景 87 5.7.3 修改dual表对查询结果的影响 88 5.8 本章实例 89 5.9 本章小结 ...
主要包括各种类型的表创建以及适用情形,如外部表、分区表、嵌套表、全局临时表等;完整性约束的管理;索引,包括B树索引、基于函数的索引、位图索引、反向索引、降序索引、压缩索引等的使用方法及其适用情形等。在...
你或许能看到所有的和数据库文件相关的数据都放在一个路径,或者分区里,所有的索引也是在一个路径下,同样系统和临时表空间也是如此。重做日志能够放在两个位置。你或许看到上百的表空间都有一到两个文件,你能看到...
目录 前言 1 简介 课程目标 1-2 ...临时表:注意事项 7-43 数据字典:概览 7-44 数据字典视图 7-45 数据字典:用法示例 7-46 小结 7-47 练习概览:管理方案对象 7-48 。。。。
11 管理表 目标 11-2 储存用户数据 11-3 Oracle 数据类型 11-5 ROWID 格式 11-8 行的结构 11-10 创建一张表 11-11 创建临时表 11-13 创建表:指南 11-14 修改储存参数 11-15 手工分配片 11-16 重构非分区表 11-17 ...
*存在递归查询情况如果关联Table太多对性能会造成较大影响,往往推荐采用临时表转为分步骤操作提高性能 *尽量使用表关联查询而不使用函数,但涉及类似于代码表要重复关联多次取数据问题时候又适合使用函数
oracle 游标的所有用法 普通循环读取,for循环读取等等
主要包括各种类型的表创建以及适用情形,如外部表、分区表、嵌套表、全局临时表等;完整性约束的管理;索引,包括B树索引、基于函数的索引、位图索引、反向索引、降序索引、压缩索引等的使用方法及其适用情形等。在...
当查询中多次用到某一部分时,可以用Oracle with语句创建一个公共临时表。因为子查询在内存临时表中,避免了重复解析,所以执行效率会提高不少。临时表在一次查询结束自动清除。 一般语法格式: 代码如下:with ...
如下所示 忽略以#开头的注释部分 : cams:/u01/app/oracle/product/8.1.7:Y 其中 cams 为实例 ID /u01/app/oracle/product/8.1.7为 ORACLE_HOME目 录 Y表示允许使用 dbstart和 dbshut 启动和关闭该实例...
7.8.7 恢复丢失的临时表空间 7.8.8 只读表空间丢失 7.8.9 索引表空间丢失 7.9 恢复联机重做日志 7.9.1 联机重做日志组某个成员丢失 7.9.2 非活动的重做日志组丢失 7.9.3 活动重做日志组丢失 7.10 恢复丢失的...
§3.1.3 建立临时表结构 97 §3.3 修改表结构 98 §3.3.1 修改表结构命令 98 §3.3.2 修改表结构例子 99 §3.3.3 删除表结构 101 §3.3.4 使用CHECK作限制约束 101 §3.3.5 使用UNRECOVERABLE创建表 102 §3.3.6 将...
可能造成CPU使用率高的情况有:大量排序、大量SQL解析、全表扫描、Oracle Bug等。因此希望找到占用CPU较高的进程ID(UNIX或LINUX)或线程ID(Windows)来找到对应的SQL语句,以分析问题的原因。 三、处理步骤 ...