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

    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

LVL 102
Mike McCrackenSenior ConsultantAsked:
Who is Participating?
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.

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.)

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
Mike McCrackenSenior ConsultantAuthor 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.

David Johnson, CDRetiredCommented:
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+'

Open in new window

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

Open in new window

Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

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
Mike McCrackenSenior ConsultantAuthor Commented:
I am not working in the database but rather the SAP universe development tool.  There is no IF available just the CASE.

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.
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.