动态条件增长场景下SQL查询性能优化相关问题解答

用户需要对指定列分组求和，通过Where条件传入列对应取值来限制返回行数。

• 以下SQL查询的返回列可根据用户需求动态指定，同时Group by分组列也支持用户动态选择。
• Where条件会根据用户的选择动态扩展。

SELECT transactionLine.department,
       transactionLine.class,
       transactionLine.cseg1,
       transactionLine.cseg_grant,
       transactionLine.cseg_restriction,
       transactionLine.cseg_revtype,
       transactionLine.cseg_rev_sub,
       transactionLine.cseg_timerestrict,
       transactionLine.cseg_fun_exp,
       transactionLine.cseg_region,
       BUILTIN_RESULT.TYPE_FLOAT(SUM(((NVL(CASE
                                               WHEN transaction.posting = 'T' THEN TO_NUMBER(transactionaccountingLine.amount)
                                           END, 0) + NVL(CASE
                                                             WHEN transaction.recordType = 'purchaseorder' THEN TO_NUMBER(transactionaccountingLine.amount)
                                                         END, 0)) - NVL(CASE
                                                                            WHEN ((transaction.recordType = 'vendorbill'
                                                                                   AND BUILTIN.DF(transactionLine.createdfrom) IS NOT NULL)
                                                                                  AND UPPER(BUILTIN.DF(transaction_0.status)) NOT LIKE '%CLOSED%')
                                                                                 AND UPPER(BUILTIN.DF(transaction_0.status)) NOT LIKE '%FULLY BILLED%' THEN TO_NUMBER(transactionaccountingLine.amount)
                                                                        END, 0)) + NVL(CASE
                                                                                           WHEN (transaction.recordType = 'vendorbill'
                                                                                                 AND transaction.posting = 'F')
                                                                                                AND BUILTIN.DF(transactionLine.createdfrom) IS NOT NULL THEN TO_NUMBER(transactionaccountingLine.amount)
                                                                                       END, 0))) AS SUMNVLCASEWHENposting
FROM TRANSACTION,
     account,
     transactionaccountingLine,
     TRANSACTION transaction_0,
                 transactionLine
WHERE ((((transactionaccountingLine.account = account.id(+)
          AND (transactionLine.transaction = transactionaccountingLine.transaction
               AND transactionLine.id = transactionaccountingLine.transactionline))
         AND transactionLine.createdfrom = transaction_0.id(+))
        AND transaction.id = transactionLine.transaction))
  AND ((transactionLine.department= '11' 
  AND transactionLine.cseg_grant  ='1'
  AND transactionLine.class = '12' 
  AND transactionLine.cseg_restriction = '1'
  AND transactionLine.cseg_revtype = '1'
  AND transactionLine.cseg1 = '4' 
  AND transactionLine.cseg_rev_sub = '1'
  AND transactionLine.cseg_timerestrict = '1'
  AND transactionLine.cseg_fun_exp = '4'
  AND transactionLine.cseg_region = '1') OR (transactionLine.department= '12' 
  AND transactionLine.cseg_grant  ='1'
  AND transactionLine.class = '12' 
  AND transactionLine.cseg_restriction = '1'
  AND transactionLine.cseg_revtype = '1'
  AND transactionLine.cseg1 = '4' 
  AND transactionLine.cseg_rev_sub = '1'
  AND transactionLine.cseg_timerestrict = '1'
  AND transactionLine.cseg_fun_exp = '4'
  AND transactionLine.cseg_region = '1'))
  AND ((NVL(account.custrecord_bm_budgetaccount, 'F') = 'F'
        AND (NOT(UPPER(transaction.status) IN ('PURCHORD:H', 'PURCHORD:G', 'PURCHORD:A', 'PURCHORD:P'))
             OR UPPER(transaction.status) IS NULL)
        AND UPPER(account.accttype) IN ('COGS',
                                        'DEFEREXPENSE',
                                        'EXPENSE',
                                        'OTHEXPENSE')))
GROUP BY transactionLine.class,
         transactionLine.department,
         transactionLine.cseg_grant,
         transactionLine.cseg_restriction,
         transactionLine.cseg_revtype,
         transactionLine.cseg1,
         transactionLine.cseg_rev_sub,
         transactionLine.cseg_timerestrict,
         transactionLine.cseg_fun_exp,
         transactionLine.cseg_region

问题1：当查询的Group by条件数量较多时，是否会对SQL查询性能产生影响？ #

会产生明显影响。Group by执行时需要对所有分组列做排序或者哈希聚合操作，分组列越多，排序/哈希计算需要处理的字段总长度越大，占用的内存和CPU资源也会越高，查询耗时会同步上升。如果分组列没有合适的索引覆盖，数据库还需要扫描全表读取所有需要的列，性能下降会更显著。

问题2：本示例中通过Where条件限制查询返回结果，该条件会随着用户对不同列值的选择不断扩展，这种实现方式是否会带来性能问题？该SQL查询可通过哪些调优手段提升执行性能？ #

性能问题分析 #

这种多个等值条件组合后用OR拼接的动态扩展方式确实存在性能隐患：

• 随着OR分支增加，数据库优化器很容易选错执行计划，放弃走索引转而全表扫描
• 多个等值条件组合如果没有匹配的联合索引，过滤效率会非常低
• 本示例中的OR分支大部分条件完全重复，仅department取值不同，属于冗余写法，本身就会增加优化器的解析成本

具体调优方案 #

1. 简化WHERE条件写法：把重复的条件提出来，示例中的OR部分可以直接简化为transactionLine.department IN ('11','12')加其他共同的等值条件，大幅降低条件复杂度，让优化器更容易识别可用索引。
2. 建立适配的联合索引：在transactionLine表上，按照「常用过滤列+分组列」的顺序创建联合索引，比如(department, class, cseg_grant, cseg_restriction, cseg_revtype, cseg1, cseg_rev_sub, cseg_timerestrict, cseg_fun_exp, cseg_region)，既可以匹配WHERE的等值过滤逻辑，也能覆盖GROUP BY的所有分组列，避免回表查询。
3. 替换过时的表连接语法：把SQL里的逗号连接和(+)左连接语法改成标准的JOIN ON写法，可读性更高，也能减少优化器解析错误的概率。
4. 提前过滤数据集：把transaction、account表的过滤条件提前到子查询里执行，先缩小关联的数据集大小，再和transactionLine等表做关联。
5. 简化SELECT计算逻辑：合并重复的NVL、CASE判断逻辑，如果业务允许可以把常用计算逻辑做成生成列，甚至提前物化存储，减少查询时的实时计算开销。
6. 动态SQL适配索引：如果用户选择的过滤列不固定，可以根据用户选择的过滤列动态生成查询语句，优先走和过滤列匹配的索引，避免固定索引不匹配的问题。

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