合并Oracle查询语句并优化性能，解决INTERFACE过滤异常问题

一、合并两条查询语句提升性能 #

原两条查询分别筛选非Interface录入且Source Interface = TBG、非Interface录入且System Booking ID不为空的交易，可通过合并关联条件与过滤逻辑，将两条语句整合为单条，减少重复扫描表的开销：

SELECT DISTINCT ab.deal_num
FROM history_table abh
INNER JOIN deal_table ab 
    ON abh.tran_num = ab.tran_num
    AND ab.tran_type = 0
INNER JOIN ins i 
    ON ab.ins_type = i.id_number
    AND i.id_number IN (605, 610)
INNER JOIN personnel_table p 
    ON abh.personnel_id = p.id_number
    AND UPPER(p.name) NOT LIKE 'INTERFACE%'
-- 关联两种交易信息类型
INNER JOIN tran_info_types tit 
    ON tit.type_name IN ('Source Interface', 'System Booking ID')
LEFT OUTER JOIN ab_tran_info ati 
    ON tit.type_id = ati.type_id
    AND ab.tran_num = ati.tran_num
WHERE abh.row_creation > TO_DATE('16-Mar-23 22:56:16', 'DD-MM-YY HH24:MI:SS') - 90
    AND abh.version_number = 1
    -- 满足任一筛选条件
    AND (
        (tit.type_name = 'Source Interface' AND ati.value = 'TBG')
        OR (tit.type_name = 'System Booking ID' AND ati.value IS NOT NULL)
    )

说明：

• 用IN关联两种type_name，避免两次重复关联表
• 通过OR整合两个筛选逻辑，确保满足任一条件即可
• 添加DISTINCT避免同一交易因匹配两种类型而重复返回

二、排查并修复INTERFACE录入交易过滤失效问题 #

问题分析 #

执行含UPPER(p.name) NOT LIKE 'INTERFACE%'的查询仍返回INTERFACE录入的交易，可能原因：

• p.name中存在特殊格式，比如带前后空格（如' INTERFACE'、'INTERFACE '）、特殊字符，导致NOT LIKE 'INTERFACE%'无法匹配
• 录入交易的标识并非仅通过personnel_table.name判断，可能存在其他字段标记INTERFACE录入

修复方案 #

1. 先排查数据，确认INTERFACE录入交易的name格式：

SELECT DISTINCT UPPER(p.name)
FROM history_table abh
INNER JOIN personnel_table p ON abh.personnel_id = p.id_number
WHERE UPPER(p.name) LIKE '%INTERFACE%'

2. 根据排查结果调整过滤条件，比如改用全模糊匹配覆盖前后内容：

AND UPPER(p.name) NOT LIKE '%INTERFACE%'

3. 若仍无效，可改用NOT EXISTS逻辑，确保完全排除INTERFACE相关记录：

SELECT ab.deal_num
FROM history_table abh
INNER JOIN deal_table ab 
    ON abh.tran_num = ab.tran_num
    AND ab.tran_type = 0
INNER JOIN ins i 
    ON ab.ins_type = i.id_number
    AND i.id_number IN (605, 610)
LEFT OUTER JOIN ab_tran_info ati 
    ON ab.tran_num = ati.tran_num
LEFT OUTER JOIN tran_info_types tit 
    ON tit.type_id = ati.type_id
WHERE abh.row_creation > TO_DATE('16-Mar-23 22:56:16', 'DD-MM-YY HH24:MI:SS') - 90
    AND abh.version_number = 1
    -- 用NOT EXISTS排除INTERFACE录入
    AND NOT EXISTS (
        SELECT 1
        FROM personnel_table p
        WHERE abh.personnel_id = p.id_number
        AND UPPER(p.name) LIKE '%INTERFACE%'
    )

内容的提问来源于stack exchange，提问作者Skywalker