CODEWITHSUNDEEP
sqloracle-databasecasequery-optimizationnvl
Atakan Günay
Atakan Günay

Reputation: 27

oracle Sql nvl or case which one is faster?

I want to talk about a scenario that we can use both nvl or case in the select statement. In PL-SQL using nvl will be easier that is true. But when it comes to query (in select statement)

will using nvl make it slower? I have a package that have loads of select statement with NVl. If I replace them with case will it work faster?

For example;

select  case sum(nvl(birikim,0)) when null then 0 else sum(nvl(birikim,0) end

instead of using this,

select nvl(sum(nvl(birikim, 0)), 0))

Upvotes: 0

Views: 4641

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

First, you are looking for a micro-optimization. The expense of a SQL query is usually in the data movement, not in the particular actions taken on the data within one row (unless you are working with large string or blobs or user-defined functions or something like that).

Second, running a function inside the SUM() is going to incur overhead for every row. That said, you may not be able to measure the effect unless you are measuring on a very large table.

Third, you don't need to worry about NULL values in the SUM(). So you can write your logic as:

select nvl(sum(birikim), 0)

Although I prefer coalesce() because it is the SQL Standard function for this purpose:

select coalesce(sum(birikim), 0)

Upvotes: 2

Rui Costa
Rui Costa

Reputation: 437

I doubt you will see a great performance increase. Built in functions are very optimized. On the other hand, any function that you use on SELECT statement will have impact in performance, unless your indexes take the function in consideration.

My suggestion: run an EXPLAIN PLAN on both queries to check the performance.

If there is not that much difference, take readability into consideration. Having NVL instead of CASE will make it more readable. Sometimes you have to balance performance with maintainability.

Upvotes: 2

Related Questions