​ 用一个开发案例详解Oracle临时表

时间:2021-12-23 02:03:58
用一个开发案例详解Oracle临时表
2016-11-14 bisal ITPUB
一、开发需求 
最近有一个开发需求,大致需要先使用主表,或主表和几张子表关联查询出ID(主键)及一些主表字段,然后再用这些ID查找最多10张表中对应的记录,主表记录数大约2000万,每张子表的记录数均为百万以上,最多可能会有5000万,主表一条数据可能对应子表多条数据。现在开发使用的逻辑是: 
使用条件查询主表或主表和几张子表(不同场景)符合条件的主表记录ID值及其他一些主表字段项。 
利用这些主表ID值,分别和几张子表使用IN子句,查询出子表中符合条件的记录项。有几张子表,就执行几次SQL语句。
这么做的弊端是: 
由于(1)查出的ID值最多可能会有100个以上,因此子表使用IN子句的时候很有可能导致CBO选择全表扫描,虽然从理论上说,一条SQL未必适用索引扫描效率就一定高,CBO一定是基于现有的统计信息选择一条成本值最低的执行计划,但一张百万级甚至千万级的表,全表扫描的效率可想而知(这儿我们不较真,可能通过SSD、Exadata硬件层面的使用能提高全表扫描的效率,此处只讨论一般存储条件下可行的方案)。另外,就是场景需要几张子表,就会执行几次SQL,一个场景下可能需要执行很多次SQL语句。
综合需求,可能至少有以下几种改进方案: 
1.使用一条SQL完成上述需求。 
主表和所有子表采用join关联的方式。 
两表两表做join,又由于主子表之间是一对多的关系,很可能造成结果集因为笛卡尔积变得很大,应用处理出现内存溢出的错误。 
使用union all的方式关联子表,作为VIEW,然后和主表做关联,这是罗大师推荐的方式,例如:
SELECT A.ID, A.NAME 
FROM 
T_ZHUBIAO A, 
(SELECT ID, NAME FROM T_ZIBIAO1 UNION ALL SELECT ID, NAME FROM T_ZIBIAO2) B 
WHERE A.NAME = 'A' AND A.ID = B.ID; 
和(1.1)的区别就是每一张子表的检索都是一次独立的索引唯一扫描,所有子表关联后作为VIEW,和主表做一次嵌套循环连接。但据了解,需求中每张子表的字段基本都不相同,有的子表选择字段有几十个,这么一来,使用这种UNION ALL需要检索字段类型相同,开发拼接起来就比较费劲,不灵活。 
2.将(1)的结果集存入一张临时表(temporary table,不是应用自行处理的普通表),相当于临时结果集,每次子表都是和这张临时表做两表关联查询,这么做可以避免因为IN值太多导致的低效检索,同时由于两表关联字段均为主键或外键(设置索引),可以使用索引扫描检索,采用交易级别控制的临时表,可以在完成本次交易后让Oracle自动清空数据,同时session之间数据隔离。 
3.(1)不变,只是(2)中每次子表查询,由应用控制,例如每30个IN值执行一条SQL语句,将一次子表查询拆分为若干次查询,好处是每次可以使用外键索引扫描检索结果集,坏处就是无形中又多了N次SQL语句的执行。
综上三种方案,(1)由于潜在的结果集过大的问题以及灵活性问题,被开发否了,目前采用的是方案(3),因为其对开发的改造较小,仅需要拆分IN语句,如果检索效率较高,测试结论符合非功能要求,就采用这种方式,若不满足要求,则会考虑使用方案(2)。
就我来说,如果能满足需求,方案1是最好的,使用合适的索引完成一次检索,减少了应用和数据库之间的交互次数,但可能这种业务需求确实很复杂,获取信息方面确实要求比较高。其次是方案2,虽然子表执行SQL次数未变,但通过临时表,可以保证每次检索均可以使用索引快速定位,避免大表的全表扫描,同时临时表特性对应用几乎透明。方案3,唯一的好处就是避免了大表的全表扫描,但代价是会多一些SQL交互,至于究竟是否可以弥补性能上的差异,只能待性能测试的结论来看了。
如果各位对上述需求有更好的解决方案,或是上述方案仍有问题,还请不吝指正!
二、临时表介绍和实验 
需要缓存中间结果集的场景,可以考虑使用临时表,因为临时表中的数据是session级别私有,每个session仅能看见和修改自己的数据,在session结束的时候,表中数据会被自动删除,无需应用操作。创建临时表使用的是CREATE GLOBAL TEMPORARY TABLE语法,ON COMMIT子句则决定了表数据是交易级别还是session级别,默认是交易级别。可以对临时表创建索引、视图或触发器。
ON COMMIT子句的两种参数区别如下: 
临时表中的数据默认存储于默认的临时表空间,可以创建过程中指定其他的临时表空间。临时表的数据和索引在定义的时候不会分配段,只有使用INSERT(CTAS)插入语句的时候,才会开始分配段空间。
创建交易级别临时表:
SQL> create global temporary table test (id number, name varchar2(10)) on commit delete rows;
查看表属性,TEMPORARY指定为Y,说明是临时表,没有tablespace_name参数值,说明不是使用普通表空间存储。
SQL> select table_name, tablespace_name, temporary from dba_tables where owner='BISAL';
TABLE_NAME  TABLESPACE_NAME  TEM
---------------- --------------------        ---
TEST                                            Y
session 1执行:
SQL> insert into test values(1, 'a');
SQL> select * from test;
ID NAME
-- ---- 
1 a
session 2执行:
SQL> select * from test;
no rows selected
说明临时表数据session级别隔离,
session 1执行:
SQL> commit;
SQL> select * from test;
no rows selected
执行commit结束交易,Oracle会自动删除临时表中数据。
创建session级临时表:
SQL> create global temporary table test (id number, name varchar2(10)) on commit preserve rows;
表属性相同:
SQL> select table_name, tablespace_name, temporary from dba_tables where owner='BISAL';
TABLE_NAME   TABLESPACE_NAME  TEM
--------------     --------------------       ---
TEST                                             Y
session 1执行:
SQL> insert into test values(1, 'a');
SQL> select * from test;
ID NAME
-- ---- 
1   a
session 2执行:
SQL> select * from test;
no rows selected
session 1执行:
SQL> commit;
SQL> select * from test;
ID NAME
-- ---- 
1   a
执行commit后,数据未删除。退出当前session再登陆,发现数据已被删除了:
SQL> select * from test;
no rows selected
总结
临时表使用起来其实很简单,除了一些语法上和普通建表语句有些不同,对应用来说就可以当作普通表使用,但其实还是有一些细节需要注意:
临时表默认使用的是默认临时表空间,如果应用会有很多排序等需要耗费临时表空间的场景,而且临时表使用频率很高,那么为了避免互相影响,可以考虑为临时表建一个独立的临时表空间。 


如果使用session级别的临时表,且应用使用了连接池,则需要确保应用完成一次交易过程中使用的是同一session,避免违反临时表使用规则。

​ 用一个开发案例详解Oracle临时表的更多相关文章

  1. 详解Oracle临时表的几种用法及意义

    Oracle临时表可以说是提高数据库处理性能的好方法,在没有必要存储时,只存储在Oracle临时表空间中.希望本文能对大家有所帮助. 1 .前言 Oracle Logo 目前所有使用 Oracle 作 ...

  2. 数据库开发-pymysql详解

    数据库开发-pymysql详解 作者:尹正杰 版权声明:原创作品,谢绝转载!否则将追究法律责任. 一.Python支持的MySQL驱动 1>.什么是驱动 与MySQL通信就是典型的CS模式.Se ...

  3. Cocos2d-x 3.X手游开发实例详解

    Cocos2d-x 3.X手游开发实例详解(最新最简Cocos2d-x手机游戏开发学习方法,以热门游戏2048.卡牌为例,完整再现手游的开发过程,实例丰富,代码完备,Cocos2d-x作者之一林顺和泰 ...

  4. 免费的HTML5连载来了《HTML5网页开发实例详解》连载(二)

    最近新浪.百度.腾讯.京东.大众点评.淘宝等流行的网站都加大了招聘HTML5的力度,HTML5开发人员成了抢手货,本次连载的是由大众点评前端工程师和一淘网前端工程师基情奉献的<HTML5网页开发 ...

  5. 免费的HTML5连载来了《HTML5网页开发实例详解》连载(六)媒体查询

    响应式设计的另一个重要技术手段是媒体查询.如果只是简单的设计一个流式布局系统,那么可以保证每个网格按比例的放大和缩小,但有可能会使得在小屏幕下(如手机设备)网格太小而严重影响阅读,这样的设计称不上响应 ...

  6. 免费的HTML5连载来了《HTML5网页开发实例详解》连载(五)图解通过Fiddler加速开发

    Fiddler是Windows底下最强大的请求代理调试工具,监控任何浏览器的HTTP/HTTPS流量,窜改客户端请求和服务器响应,解密HTTPS Web会话,图4.44为Fiddler原理示意图. 图 ...

  7. 《HTML5网页开发实例详解》连载(四)HTML5中的FileSystem接口

    HTML 5除了提供用于获取文件信息的File对象外,还添加了FileSystem相关的应用接口.FileSystem对于不同的处理功能做了细致的分类,如用于文件读取和处理的FileReader和Fi ...

  8. 当里个当,免费的HTML5连载来了《HTML5网页开发实例详解》连载(一)

    读懂<HTML5网页开发实例详解>这本书 你还在用Flash嘛?帮主早不用了 乔布斯生前在公开信“Flash之我见”中预言:像HTML 5这样在移动时代中创立的新标准,将会在移动设备上获得 ...

  9. spring的IOC,DI及案例详解

    一:spring的基本特征 Spring是一个非常活跃的开源框架:它是一个基于Core来架构多层JavaEE系统的框架,它的主要目的是简化企业开发.Spring以一种非侵入式的方式来管理你的代码,Sp ...

随机推荐

  1. Appium移动自动化测试(四)--one demo

    继续更新. -------------------------------------------- 第四节  安装Appium Client Appium Client是对webdriver原生ap ...

  2. 如何把bootstrap用webpack打包

    今天下载了一个anguarl2写后台,一直没有找到是如何使用bootstrap样式的,然后就全文做了搜索,发现有一段代码 import 'bootstrap-loader'; 这段代码很可疑,所以就查 ...

  3. SQLite Code配置DbConfiguration

    [DbConfigurationType(typeof(SQLiteConfiguration))] public partial class rsapiEntities : DbContext { ...

  4. sublime 挪移的第一层(插件的安装和使用)

    人总要坚持一些什么,故从今儿始,咱也开始写博客,不为成为大神,不为成为分享大师,只为让自己快活有成就感一些 1.sublime的安装 登录sublime官网 : http://www.sublimet ...

  5. java排序算法(一):概述

    java排序算法(一)概述 排序是程序开发中一种非常常见的操作,对一组任意的数据元素(活记录)经过排序操作后,就可以把它们变成一组按关键字排序的一组有序序列 对一个排序的算法来说,一般从下面三个方面来 ...

  6. 回文检测 &lbrack;USACO Training Section 1&period;3&rsqb;

    题目描述 据说如果你给无限只母牛和无限台巨型便携式电脑(有非常大的键盘),那么母牛们会制造出世上最棒的回文.你的工作就是去寻找这些牛制造的奇观(最棒的回文). 在寻找回文时不用理睬那些标点符号.空格( ...

  7. &lbrack;django&rsqb;梳理drf知识点2

    外键关系的自动维护 原始提交的server数据 { ... "manufacturer": "DELL", "model_name": &q ...

  8. jqgrid 启用键盘操作bindKeys

    给jqgrid启用键盘操作,代码如下: // the bindKeys() 启用键盘操作 $("#jqGrid").jqGrid('bindKeys'); 启动后,比如可以使用上下 ...

  9. 前端:jQuery笔记

    前端:jQuery笔记 此系列文章乃是学习jQuery的学习笔记. Asp.net MVC Comet推送 摘要: 一.简介 在Asp.net MVC实现的Comet推送的原理很简单. 服务器端:接收 ...

  10. &lbrack;BZOJ2117&rsqb;Crash的旅游计划

    Description 眼看着假期就要到了,Crash由于长期切题而感到无聊了,因此他决定利用这个假期和好友陶陶一起出去旅游. Crash和陶陶所要去的城市里有N (N > 1) 个景点,Cra ...