在 WHERE 子句中调整 DECODE() 语句的性能 [英] Performance tuning a DECODE() statement in a WHERE clause

问题描述

我重新编写了一个查询以减少选择记录所需的时间.但是我仍然看到需要在解码线中进行一些小的调整，因为成本很高.有人可以让我知道是否可以在没有解码功能的情况下重写相同的查询吗?删除 decode 函数的目的是使用 v_id 列中的索引.

I re-wrote a query to reduce the time it takes to pick the records. But still I see that there is a small tuning that needs to be done in the decode line as the cost is high. Can someone please let me know if the same query can be re-written without the decode functionality? The purpose of removing the decode function would be to use the index in v_id column.

到目前为止我所尝试的.

What I have tried so far.

1. 尝试创建函数索引(知道不能使用绑定变量)但失败了.
2. 已尝试使用 OR 条件，但它不会选择索引.因此，任何建议都会有很大帮助.

查询如下:

SELECT SUM(NVL(dd.amt,0))
  FROM db,dd
WHERE db.id = dd.dsba_id
  AND dd.nd_id = xxxxxxx
  AND dd.a_id = 'xxxxx-xx'
  AND DECODE (db.v_id , xxxxxxxxx, 'COMPLETE' , db.code ) = 'COMPLETE'
  AND db.datet BETWEEN TRUNC ( SYSDATE , 'YEAR' ) AND SYSDATE;

推荐答案

我建议将代码编写为:

SELECT SUM(dd.amt)
FROM db JOIN
     dd
     ON db.id = dd.dsba_id
WHERE dd.nd_id = xxxxxxx AND
      dd.a_id = 'xxxxx-xx' AND
      (db.v_id = xxxxxxxxx OR db.code = 'COMPLETE') AND
      db.datet >= trunc(sysdate, 'YEAR');

对于这个查询，我会推荐索引:

For this query, I would recommend indexes on:

• db(nd_id, a_id, id, datet, code)
• dd(dsba_id, datet, v_id)

上述查询的变化:

• 从不在 FROM 子句中使用逗号.始终使用正确、明确、标准、可读的JOIN 语法.(不过，这不会影响性能.)
• decode() 很难理解.一个简单的布尔值 or 是等效的.
• BETWEEN 是不必要的，假设 datet 不在未来.
• SUM(NVL()) 不需要，因为 NULL 值被忽略.如果您担心 NULL 结果，我建议 COALESCE(SUM(dd.amt), 0)

• Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax. (This does not affect performance, however.)
• decode() is rather hard to follow. A simple boolean or is equivalent.
• BETWEEN is unnecessary assuming that datet is not in the future.
• SUM(NVL()) is not needed, because NULL values are ignored. If you are concerned about NULL result, I would suggest COALESCE(SUM(dd.amt), 0)

这篇关于在 WHERE 子句中调整 DECODE() 语句的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！