|
|
|
|
移动端

从一个开发需求的解决方案看Oracle临时表

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

作者:bisal来源:ITPUB|2018-03-14 19:39

人工智能+区块链的发展趋势及应用调研报告


从一个开发需求的解决方案看Oracle临时表

一、开发需求 

最近有一个开发需求,大致需要先使用主表,或主表和几张子表关联查询出 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,然后和主表做关联,这是罗大师推荐的方式,例如:

  1. SELECT A.ID, A.NAME   
  2. FROM   
  3. T_ZHUBIAO A,   
  4. (SELECT ID, NAME FROM T_ZIBIAO1 UNION ALL SELECT ID, NAME FROM T_ZIBIAO2) B   
  5. 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) 插入语句的时候,才会开始分配段空间。

创建交易级别临时表:

  1. SQL> create global temporary table test (id number, name varchar2(10)) on commit delete rows

查看表属性,TEMPORARY 指定为 Y,说明是临时表,没有 tablespace_name 参数值,说明不是使用普通表空间存储。

  1. SQL> select table_name, tablespace_name, temporary from dba_tables where owner='BISAL' 
  2. TABLE_NAME  TABLESPACE_NAME  TEM  
  3. ---------------- --------------------        ---  
  4. TEST                                            Y 

session 1 执行:

  1. SQL> insert into test values(1, 'a');  
  2. SQL> select * from test;  
  3. ID NAME  
  4. -- ----   
  5. 1 a 

session 2 执行:

  1. SQL> select * from test;  
  2. no rows selected 

说明临时表数据 session 级别隔离,

session 1 执行:

  1. SQL> commit 
  2. SQL> select * from test;  
  3. no rows selected 

执行 commit 结束交易,Oracle 会自动删除临时表中数据。

创建 session 级临时表:

  1. SQL> create global temporary table test (id number, name varchar2(10)) on commit preserve rows

表属性相同:

  1. SQL> select table_name, tablespace_name, temporary from dba_tables where owner='BISAL' 
  2.  
  3. TABLE_NAME   TABLESPACE_NAME  TEM  
  4. --------------     --------------------       ---  
  5. TEST                                             Y 

session 1 执行:

  1. SQL> insert into test values(1, 'a');  
  2. SQL> select * from test;  
  3. ID NAME  
  4. -- ----   
  5. 1   a 

session 2 执行:

  1. SQL> select * from test;  
  2. no rows selected 

session 1 执行:

  1. SQL> commit 
  2. SQL> select * from test;  
  3. ID NAME  
  4. -- ----   
  5. 1   a 

执行 commit 后,数据未删除。退出当前 session 再登陆,发现数据已被删除了:

  1. SQL> select * from test;  
  2. no rows selected 

总结

临时表使用起来其实很简单,除了一些语法上和普通建表语句有些不同,对应用来说就可以当作普通表使用,但其实还是有一些细节需要注意:

1. 临时表默认使用的是默认临时表空间,如果应用会有很多排序等需要耗费临时表空间的场景,而且临时表使用频率很高,那么为了避免互相影响,可以考虑为临时表建一个独立的临时表空间。

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

【编辑推荐】

  1. 数据库紧急恢复过程,快来看看!
  2. 理解数据库连接池底层原理之手写实现
  3. 以MySQL为例,带你从原理上理解那些所谓的数据库军规
  4. Oracle数据不同步的问题分析和解决思路
  5. StackOverflow 调查:2018 年最流行的数据库分析
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

Java for Flash动态网站开发手札

本书深入浅出地说明了如何利用Java、Flash及XML进行Flash富媒体应用程序的开发。 本书知识丰富,内容结构合理,包括:Flash影片应用程序与...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊