CODEWITHSUNDEEP
sqloracle-databasecasewhere-clause
Alistair Swanson
Alistair Swanson

Reputation: 1

Why might a CASE expression be more efficient than a WHERE clause?

Can anyone suggest why my particular query on a table with hundreds of millions of rows works more efficiently when I put what would typically be the WHERE clause into a CASE expression?

Logically you do: 

SELECT
  NOTES_TABLE.CASE_ID,
  NOTES_TABLE.CASE_NOTE,
  NOTES_TABLE.ACTIVITY_NAME,
  NOTES_TABLE.CREATED_DATETIME
FROM
  NOTES_TABLE
WHERE
  NOTES_TABLE.ACTIVITY_NAME  =  'Some Activity'
  AND NOTES_TABLE.CREATED_DATETIME > '01 Jan 2015 00:00:00'
  AND NOTES_TABLE.CASE_NOTE='Some Activity has been processed'

However, I've noticed that: 

select t1.* FROM 
(
  SELECT
    NOTES_TABLE.CASE_ID,
    case 
      when NOTES_TABLE.CASE_NOTE='Some Activity has been processed' 
      then NOTES_TABLE.CASE_NOTE else null 
    end as NOTES_TABLEs,
    NOTES_TABLE.ACTIVITY_NAME,
    NOTES_TABLE.CREATED_DATETIME
  FROM
    NOTES_TABLE
  WHERE
    NOTES_TABLE.ACTIVITY_NAME  =  'Some Activity'
    AND NOTES_TABLE.CREATED_DATETIME > '01 Jan 2015 00:00:00'
) t1 
WHERE t1.NOTES_TABLEs is not null

runs in a matter of seconds rather than 30+ minutes.

This question has bugged me for a while, but unfortunately I don't have direct database access (using Infoview front-end) so I cannot get an explain plan. My curiosity is not really a suitable reason for a service request to get my supplier to explain.

Upvotes: 0

Views: 96

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

This is too long for a comment.

The difference in performance is, no doubt, due to using an index versus a full table scan. Oracle generally has a good optimizer, so it is surprising that it would miss such an optimization opportunity.

30+ minutes for a query that uses only one table is an inordinately long time. This suggests that your table has many billions of rows and you have a pretty slow computer. How large is the table? How slow is your processor?

The other possibility is that other operations were taking place on the server, such as a checkpoint or data modifications on the table.

Upvotes: 1

Related Questions