关于重复性校验的一条 SQL 优化问题请教

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

已注册用户请登录

这是一个创建于 2380 天前的主题，其中的信息可能已经有所发展或是发生改变。

需求是用文件导入的方式批量对这张表进行新增操作，但导入时需要对数据执行一系列校验。
其中有一个步骤是 重复性校验。若同时满足以下条件，则说明导入的数据已经存在，校验失败：

FILED_A、FILED_B、FILED_C、FILED_D 四个字段完全相同；
导入数据的时间区间（ STARTTIME ~ ENDTIME ）与已经存在的数据时间有交叉（如数据 A [20171101 ~ 20171103]与数据 B [20171102 ~ 20171104] 视为交叉；
导入数据的 LOCATION 与已经存在的数据有交叉（如数据 A [北京|上海] 与数据 B[上海|杭州] 视为交叉）。

原先的方案是在内存中比较，但由于导入的数据往往较大，如果取一条数据查一次数据库看存不存在，5000 条数据就要查 5000 次，性能比较差。于是考虑把待校验的数据放在一张临时表中，一次性对所有数据进行对比。

临时表与正式表结构基本一致，但多了下面一个字段：

TEMP_FOO
| 字段　　　　　| 类型　　　　　| 说明　　　|
| CHECKRESULT | VARCHAR2(20) | 校验结果　|

目前写出的重复性校验 SQL 如下（如果临时表 TEMP_FOO 待校验的数据已经存在正式表 FOO 中，则将其校验结果字段更新为 Repeat ）：

UPDATE TEMP_FOO t
SET t.CHECKRESULT = 'Repeat'
WHERE EXISTS (
    SELECT 1 FROM FOO o
    WHERE regexp_like(o.LOCATION, t.LOCATION)
    AND o.STARTTIME >= concat(to_char(to_date(t.STARTTIME,'yyyymmddhh24miss'),'yyyymmdd'),'000000')
    AND o.STARTTIME < concat(to_char(to_date(t.STARTTIME,'yyyymmddhh24miss')+1,'yyyymmdd'),'000000')
    AND o.ENDTIME >= concat(to_char(to_date(t.ENDTIME,'yyyymmddhh24miss'),'yyyymmdd'),'000000')
    AND o.ENDTIME < concat(to_char(to_date(t.ENDTIME,'yyyymmddhh24miss')+1,'yyyymmdd'),'000000')
    AND nvl(t.FILED_A, '0') = nvl(o.FILED_A, '0')
    AND nvl(t.FILED_B, '0') = nvl(o.FILED_B, '0')
    AND nvl(t.FILED_C, '0') = nvl(o.FILED_C, '0')
    AND nvl(t.FILED_D, '0') = nvl(o.FILED_D, '0')
    )

现在有几个问题想请教大家：

当导入数据量很大（超过 5000 条），该条 SQL 执行效率仍然很低，请问是否有办法优化？
有时候 WHERE 子句中，FILED_A、FILED_B、FILED_C、FILED_D 四个条件已经能够保证查询结果为 0 了，为什么 oracle 还是缓慢地执行了全部筛选条件？
不限于在数据库中做对比，是否还有其他方案可以高效地进行重复性校验？

有任何一点建议都可以提出，非常感谢！

字段

校验

nvl

varchar2

5 条回复 • 2017-11-17 10:10:05 +08:00