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

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

[[222506]]

一、开发需求 

最近有一个开发需求,大致需要先使用主表,或主表和几张子表关联查询出 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,避免违反临时表使用规则。 

责任编辑:庞桂玉 来源: ITPUB
相关推荐

2011-08-11 18:38:05

Oracle回滚段

2010-04-28 11:48:13

Oracle MySQ

2010-04-28 18:49:10

Oracle临时表

2010-10-28 13:53:13

ORACLE存储过程

2009-03-17 09:27:52

ITSMITIL解决方案

2018-11-05 11:06:38

openmediavaNAS 方案

2019-07-08 10:01:33

物联网IOT技术

2010-11-30 16:50:42

2012-09-04 09:40:01

2023-02-10 15:41:50

物联网物联网平台

2009-07-28 11:37:55

7类布线端口

2011-06-08 22:06:04

工作站解决方案

2011-04-13 13:56:52

Oracle临时表

2020-08-10 08:30:23

开发软件开源

2013-08-26 09:18:52

2021-04-11 09:00:13

Fes.js前端

2012-03-21 11:10:00

JDBCMySQLJava

2023-03-29 10:48:28

2010-04-30 17:33:27

Oracle数据集成

2010-05-07 16:30:01

Oracle数据集成
点赞
收藏

51CTO技术栈公众号