Performance tuning a DECODE() statement in a WHERE clause

Question

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.

Tried creating a function index (Knowing that bind variables cannot be used) and it failed.
Have tried using the OR condition but it would not pick the index. So any suggestion would be of a great help.

Query is given below.:

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;

It seems unlikely the optimizer would choose to use a function-based index anyway. I would expect the query to drive off table DD, because you've got equality access on ND_ID and A_ID, then it might choose to use an index to join to DB on ID. But the other criteria look as though they will always be filters. — APC, Jan 05 '21 at 14:05
Anyway, there isn't enough information here for anybody to do anything but guess, and that's a waste of all our time (including yours). I suggest you read [this excellent post on asking Oracle tuning questions](https://stackoverflow.com/a/34975420/146325), and maybe edit your question to provide more details. However, I also suggest you ponder whether there really is *"a small tuning that needs to be done"*. How much time to you expect to shave off the query's elapsed time? Does it really need any further tuning? Or can your users live with the current `ela t`? — APC, Jan 05 '21 at 14:10

score 1 · Answer 1 · answered Jan 05 '21 at 14:22

1

I would suggest writing the code as:

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)

The changes to the above query:

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)

answered Jan 05 '21 at 14:22

Gordon Linoff

1,242,037
58
646
786

1

Perhaps the rewrite of `decode` is equivalent in this case (for example, if the `v_id` column is declared `not null`), but in general it is not. If `v_id` and `xxxxxxxx` are both `null`, the `decode` condition will return `true` regardless of `code`; not so with your `or` rewrite. This complication is *exactly* why some people prefer `decode` in such situations; the correct rewrite (equivalent in **all** cases) is more complicated than what you have shown. – Jan 05 '21 at 14:48
But i am getting a higher cost when i re-write it as OR condition. I tried that way (Mentioned in the question). The thing is the index is not getting picked when i use OR condition too – Vimal Bhaskar Jan 05 '21 at 16:05
1

As I said earlier, **you need to post the explain plans**. Grimacing and guessing is the least effective means of tuning a query: Oracle provides a great deal of specific information about query performance precisely to avoid these sorts of conversations. – APC Jan 05 '21 at 16:16
@VimalBhaskar . . . The `decode()` versus `or` really should have no impact on the use of the indexes. Did you try the indexes specified in this answer? – Gordon Linoff Jan 05 '21 at 18:57

Performance tuning a DECODE() statement in a WHERE clause

1 Answers1

Linked