troubleshooting Question

Oracle calculation performance

Avatar of Mike McCracken
Mike McCracken asked on
Oracle Database
6 Comments1 Solution116 ViewsLast Modified:
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

    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+'

Can this be rewritten in the other CASE form as

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

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

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros