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