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
LVL 102
mlmccAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
In general, a calculation in the selected results will be evaluated only once, or perhaps once per row if the calculation involves values for that row (as in your example.)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccAuthor Commented:
Thanks.  That is what I expected but was concerned that it might evaluate each one in turn thus causing the query to run long.

mlmcc
0
David Johnson, CD, MVPOwnerCommented:
Here the code will run 1-3 times.. until it gets a match
Don't Use CASE here


DDate = A Date - Discharge Date
IF DDate < 31 THEN '000-030'
ELSE IF  DDate < 61 THEN '031-060'
Else If DDate< 91 THEN '061-090'
    ELSE    '091+'
END

Open in new window

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

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
The IF syntax doesn't apply to Oracle SQL.

You can use IF with ORACLE PL/SQL, but then the syntax would be different (ELSIF instead of ELSE IF, and END IF instead of END)

CASE can be use in SQL or PL/SQL
0
mlmccAuthor Commented:
I am not working in the database but rather the SAP universe development tool.  There is no IF available just the CASE.

mlmcc
0
Mark GeerlingsDatabase AdministratorCommented:
In most Oracle queries in most systems, the "where" clause has a much bigger impact on performance than calculations done on each row retrieved.

True, if a large number of records are retrieved, the calculations done on each record will have at least a small impact on performance.  In your case, if you know, or expect that the "91+" values will be more numerous than the "<31" values, you may want to check for the larger values first.  But, if more records are expected to be in the "<31" range, you should keep your CASE statement in the order you have it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.