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

[[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，然后和主表做关联，这是罗大师推荐的方式，例如：

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，避免违反临时表使用规则。