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

网友投稿 1117 2023-05-06

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

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

一、开发需求

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

1. 使用条件查询主表或主表和几张子表 (不同场景) 符合条件的主表记录 ID 值及其他一些主表字段项。

2. 利用这些主表 ID 值,分别和几张子表使用 IN 子句,查询出子表中符合条件的记录项。有几张子表,就执行几次 SQL 语句。

这么做的弊端是

由于 (1) 查出的 ID 值最多可能会有 100 个以上,因此子表使用 IN 子句的时候很有可能导致 CBO 选择全表扫描,虽然从理论上说,一条 SQL 未必适用索引扫描效率就一定高,CBO 一定是基于现有的统计信息选择一条成本值***的执行计划,但一张***甚至***的表,全表扫描的效率可想而知 (这儿我们不较真,可能通过 ***、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 值太多导致的低效检索,同时由于两表关联字段均为主键或外键 (设置索引),可以使用索引扫描检索,采用交易级别控制的临时表,可以在完成本次交易后让 *** 自动清空数据,同时 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) 插入语句的时候,才会开始分配段空间。

创建交易级别临时表:

查看表属性,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 执行:

创建 session 级临时表:

表属性相同:

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> select * from test;  no rows selected

总结

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

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

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

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:如何提高MySQL安全性?这简单几招来帮你
下一篇:深入浅出分析MySQL常用存储引擎
相关文章