一个开发需求的解决方案 & Oracle临时表介绍-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

一个开发需求的解决方案 & Oracle临时表介绍

简介: 一、开发需求 最近有一个开发需求,大致需要先使用主表,或主表和几张子表关联查询出ID(主键)及一些主表字段,然后再用这些ID查找最多10张表中对应的记录,主表记录数大约2000万,每张子表的记录数均为百万以上,最多可能会有5000万,主表一条数据可能对应子表多条数据。

一、开发需求
最近有一个开发需求,大致需要先使用主表,或主表和几张子表关联查询出ID(主键)及一些主表字段,然后再用这些ID查找最多10张表中对应的记录,主表记录数大约2000万,每张子表的记录数均为百万以上,最多可能会有5000万,主表一条数据可能对应子表多条数据。现在开发使用的逻辑是:
1.使用条件查询主表或主表和几张子表(不同场景)符合条件的主表记录ID值及其他一些主表字段项。
2.利用这些主表ID值,分别和几张子表使用IN子句,查询出子表中符合条件的记录项。有几张子表,就执行几次SQL语句。

这么做的弊端是:
由于(1)查出的ID值最多可能会有100个以上,因此子表使用IN子句的时候很有可能导致CBO选择全表扫描,虽然从理论上说,一条SQL未必适用索引扫描效率就一定高,CBO一定是基于现有的统计信息选择一条成本值最低的执行计划,但一张百万级甚至千万级的表,全表扫描的效率可想而知(这儿我们不较真,可能通过SSD、Exadata硬件层面的使用能提高全表扫描的效率,此处只讨论一般存储条件下可行的方案)。另外,就是场景需要几张子表,就会执行几次SQL,一个场景下可能需要执行很多次SQL语句。

综合需求,可能至少有以下几种改进方案:
1.使用一条SQL完成上述需求。
(1.1) 主表和所有子表采用join关联的方式。
两表两表做join,又由于主子表之间是一对多的关系,很可能造成结果集因为笛卡尔积变得很大,应用处理出现内存溢出的错误。
(1.2) 使用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

总结:
临时表使用起来其实很简单,除了一些语法上和普通建表语句有些不同,对应用来说就可以当作普通表使用,但其实还是有一些细节需要注意:
1.临时表默认使用的是默认临时表空间,如果应用会有很多排序等需要耗费临时表空间的场景,而且临时表使用频率很高,那么为了避免互相影响,可以考虑为临时表建一个独立的临时表空间。
2.如果使用session级别的临时表,且应用使用了连接池,则需要确保应用完成一次交易过程中使用的是同一session,避免违反临时表使用规则。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章