Avatar of Mike McCracken
Mike McCracken

asked on 

Oracle calculation performance

How does Oracle handle repeated calculations?  Is it evaluated once or is it evaluated each time it is encountered?

For instance in a case statement creating buckets for counting

CASE
    WHEN A Date - Discharge Date < 31 THEN '000-030'
    WHEN A Date - Discharge Date < 61 THEN '031-060'
    WHEN A Date - Discharge Date < 91 THEN '061-090'
    ELSE    '091+'
END

Can this be rewritten in the other CASE form as

CASE A Date - Discharge Date
    WHEN ???? THEN  '000-030'
    ...
   ELSE '091+'
END

I realize the SQL is not in Oracle SQL format.  My question is on the substance as the tool I use will put it in Oracle format when the query is built.
I know some databases will calculate the A Date - Discharge Date once and others would in this case calculate it 3 times.  This is just an example. My real statement I am working with has the calculation repeated 14 times thus my concern for speed

mlmcc
Oracle Database

Avatar of undefined
Last Comment
Mark Geerlings

8/22/2022 - Mon