How to replace NVL function in a sql query

Question

I need to replace an NVL function into a select

for example

NVL (MAX(EX.ID_EXAMPLE),0)

How can I replace this into my query

Thanks a lot

Answer 1

The premise is wrong, changing NVL in a SQL statement like this won't improve performance:

SELECT COUNT (1) EXIST, NVL (MAX(EX.CD_ID_EXAMPLE),0) CD_ID_EXAMPLE
  FROM EXAMPLE EX
 WHERE EX.CD_ID_ = :cd_id;

Compared with retrieving the data, the time to process the data through NVL is irrelevantly small. Oracle performance tuning is about how data is retrieved and joined. The time for the CPU to process small functions almost never matters. Especially in this example, where NVL will literally only run once for the entire statement.

(If the NVL was used as a condition, then things might be different. There are times when NVL, or any function, can impact cardinality estimates, and thus affect the execution plans. But not in your query.)

Another red flag is COUNT(1), one of the oldest Oracle performance tuning myths. I have a feeling you're at an organization with a lot of cargo cult programming. In the long run, you need to build a culture that uses reproducible test cases, and start eliminating these myths.

Answer 2

You may try adding the following index to your table:

CREATE TABLE idx ON EXAMPLE (CD_ID, CD_ID_EXAMPLE);

I don't even know whether adding CD_ID_EXAMPLE would even help, because you are doing a count over the entire table. So, the following alone might be the best you can do here:

CREATE TABLE idx ON EXAMPLE (CD_ID);

This would at least help Oracle quickly remove records which do not match the WHERE clause.

Answer 3

NVL (MAX(EX.ID_EXAMPLE),0)

can be replaced with CASE..WHEN as following:

CASE WHEN 
MAX(EX.ID_EXAMPLE) IS NOT NULL THEN 
MAX(EX.ID_EXAMPLE)
ELSE 0 
END

and better way is to use a subquery in which MAX is calculated and in outer query use alias in CASE..WHEN so that MAX is not calculated twice.

SELECT CASE WHEN
MAX_ID_EXAMPLE IS NULL THEN MAX_ID_EXAMPLE 
ELSE 0 
END
...
FROM (SELECT MAX(EX.ID_EXAMPLE) AS MAX_ID_EXAMPLE 
...
FROM ...)

Cheers!!