sql - 为什么这两个查询的性能差异如此之大？

Question

我的任务是根据一些规则获取特定帐户和交易。值得注意的是，账户和交易位于不同的表中，我的查询主要返回账户 - 比例约为 70 个账户比 1 笔交易。为了方便起见，我想将它们放入一个查询中 - 这是更大过程中的一个阶段。

原始查询:

SELECT DISTINCT 
    CASE 
        WHEN (a.transaction_type IN ('500', '501', '502', '920') AND a.transaction_date >= '#DATE#' AND to_char(a.transaction_date,'HH24') >= 16) THEN 'Transaction time'
        WHEN b.closing_date >= '#DATE#' THEN 'Closing time'
        WHEN b.opening_date >= '#DATE#'    THEN 'Opening time'  
        WHEN (b.type = 'X' AND b.active = 'NO') THEN 'Frozen account'   
    END AS "comment"
    ,b.branch 
    ,b.basic 
    ,b.lmt 
FROM 
    VDS.transactions a
    JOIN VDS.accounts b ON a.acct_no = b.acct_no
WHERE
    (a.transaction_type IN ('500', '501', '502', '920')
    AND a.transaction_date  >= '#DATE#'
    AND to_char(a.transaction_date,'HH24') >= 16)
    OR
    (b.closing_date >= '#DATE#'
    OR b.opening_date >= '#DATE#'   
    OR (b.type = 'X' AND b.active = 'NO'))

它似乎运行良好，尽管有些缓慢 - 它的执行时间通常在 12 秒左右。问题是 - 有时根本无法完成。看起来数据库完全卡在查询上。由于我不是 Oracle 管理员，我无法证实我的怀疑是该查询的错误，但多项测试表明确实如此。

因此，考虑到交易数量远少于账户数量，我准备了另一种变体。

带有子查询的变体:

SELECT 
    'Transaction time' AS "comment"
    ,b.branch 
    ,b.basic 
    ,b.lmt 
FROM 
    VDS.transactions a
    JOIN VDS.accounts b ON a.acct_no = b.acct_no
WHERE
    a.transaction_type IN ('500', '501', '502', '920')
    AND a.transaction_date  >= '#DATE#'
    AND to_char(a.transaction_date,'HH24') >= 16

UNION

SELECT 
    CASE 
        WHEN closing_date >= '#DATE#' THEN 'Closing time'
        WHEN opening_date >= '#DATE#'    THEN 'Opening time'    
        WHEN (type = 'X' AND active = 'NO') THEN 'Frozen account'   
    END AS "comment"
    ,branch 
    ,basic 
    ,lmt 
FROM 
    VDS.accounts 
WHERE
    closing_date >= '#DATE#'
    OR opening_date >= '#DATE#' 
    OR (type = 'X' AND active = 'NO'))

你瞧 - 执行时间减少到 3-5 秒左右，并且查询不再阻塞数据库。它还返回了稍微多一点的结果，这很奇怪，但不是问题。

所以我的最后一个问题是:有人可以向我解释一下数据库内部可能发生了什么，它很乐意接受子查询的变体，而原始查询却变得不稳定？我可以更好地理解子查询的性能，但我不知道为什么查询有时会工作，有时会完全挂起。

Answer 1

实际上，这是完全有道理的...让我们看看第一个查询的 WHERE 子句

WHERE
    (a.transaction_type IN ('500', '501', '502', '920')
    AND a.transaction_date  >= '#DATE#'
    AND to_char(a.transaction_date,'HH24') >= 16)
    OR
    (b.closing_date >= '#DATE#'
    OR b.opening_date >= '#DATE#'   
    OR (b.type = 'X' AND b.active = 'NO'))

第一部分很简单...>=给定日期且具有特定交易类型的交易。没问题。

现在，您可以将“OR”添加到“B”账户表测试的任何其他条件中，以测试已开设、已关闭或卡住的账户。

由于(交易)OR(帐户)，当您比较不关心日期的卡住条件的最终“OR”子句时，您已经打开了最多所有交易的查询(由于OR)/时间。

由于帐户上会发生开立或关闭帐户的交易，因此您知道该帐户将进行事件。如果尝试针对卡住帐户进行交易，也将被考虑在内。

这是我如何调整你的 where 子句...

WHERE
        a.transaction_date  >= '#DATE#'
    AND to_char(a.transaction_date,'HH24') >= 16)
    AND 
        (  a.transaction_type IN ('500', '501', '502', '920')
             OR
           (   b.closing_date >= '#DATE#'
            OR b.opening_date >= '#DATE#'   
            OR (b.type = 'X' AND b.active = 'NO')
        )

因此，您只考虑预期日期范围内的交易...并且其中，仅考虑交易类型为 OR(开放、关闭、卡住)的交易