Once comparing the Oracle COALESCE vs NVL performance most of the searches say those are mostly equal. But I also hope to do a complete analysis with data load for a simple example. Before moving into that let’s get to know about meaning and where to use oracle coalesce and NVL.
COALESCE is the latest function and use ANSI-92 standard but NVL is Oracle defined in the late 80’s so it is very old. The main concern I have is these functions are implemented differently. Therefore evaluating performance is important.
NVL: evaluates both arguments
COALESCE: Looking for the arguments until find the NON-NULL value (exceptions, can be there such as sequence NEXTVAL):
Coalesce: The COALESCE function in Oracle looks for the first non-null value from the expression. Once the oracle reaches non null value stop the traverse through the remaining expressions. Therefore, it is a more versatile function because it handles multiple values and also its not evaluate all full expressions.
-- Syntax: COALESCE (expr1, expr2, ..., expr_n) SELECT COALESCE(NULL, 'A', 'B') AS result FROM dual; -- Output: 'A' --
- expr1, expr2, …, expr_n: Evaluate the expression from 1 to n until find the non-null expression.
NVL: The NVL function evaluates both expressions and returns the default value if an expression is null. Otherwise, return the expression.
--Syntax: NVL(expr, default) SELECT NVL(NULL, 'A') AS result FROM dual; -- Output: 'A' --
- expr: Expression to be evaluated.
- default: default value if an expression is null.
Performance Comparison:
Example 1: Simple Replacement
-- Using NVL: SELECT NVL(column_name, 'Default') FROM sample_table; -- Using COALESCE: SELECT COALESCE(column_name, 'Default') FROM sample_table; --
For the small cases according to the above do the same thing. Therefore performance does not change in such a small situation.
Example 2: Handling Multiple Columns
-- Using NVL : --This is not support by oracle. you cant use more that 2 argument for NVL SELECT NVL(column1, column2, 'Default') FROM sample_table; --
-- Using COALESCE SELECT COALESCE(column1, column2, 'Default') FROM sample_table; --
COALESCE is more versatile when for more than 2 columns in the arguments.
Example 3: Join Conditions
If you want to use NVL or COALESCE when join table that also possible. But my concern is there can be performance drop because of if there is a index on join columns those are may not fetch. Its because of we does not use columns as it is. Therefore we have to use function base index in those type of situations. Then oracle create index with function like COALESCE
-- Using NVL SELECT * FROM table1 JOIN table2 ON table1.column = NVL(table2.column, 'Default'); -- Using COALESCE SELECT * FROM table1 JOIN table2 ON table1.column = COALESCE(table2.column, 'Default'); --
Both NVL and COALESCE can be used in the join table if you don’t have a complex function call as the default value performance is not changed.
Example 4: Handling Numeric Values
-- Using NVL SELECT NVL(numeric_column, 0) FROM sample_table; -- Using COALESCE SELECT COALESCE(numeric_column, 0) FROM sample_table; --
Both NVL and COALESCE can be used to set default values when the numeric column value returns null.
Above all the cases performance does not change. It’s because those are not complex let’s have some complex examples for performance comparison now I hope this will good example for the Oracle COALESCE vs NVL performance.
Oracle COALESCE vs NVL Performance Complete Analyze
Problem: I have a student table and fees column I would like to get the class fees from the fees column. So I have to check whether fees are available in the fees column otherwise, I have to calculate the amount in real-time and show it in the output result. I hope to add 10000 students to my table and add intentionally null for some students.
If the amount is null then I added a dummy calculation as a default calculation. This can be a function/procedure call for calculating student fees.
--
CREATE TABLE Students(
student_id VARCHAR2(50) ,
student_name VARCHAR2(100) ,
fees NUMBER,
PRIMARY KEY(student_id)
);
-- Insert sample records
declare
-- insert 100000 records into database
i integer:=0;
fees_ integer:=null;
begin
LOOP
i:= i+1;
if (MOD(i,3))= 0 then
fees_:= null;
else
fees_:= round(dbms_random.value(1000,10000),0);
end if;
INSERT INTO Students (student_id, student_name, fees)
VALUES ('S-'||i,'Name-'||i,fees_);
EXIT WHEN i > 9999;
END LOOP;
end;
--here we have 2 queries one with NVL and the other one with COALESCE
NVL Example Code
-- NVL
SELECT student_id, student_name, (SELECT SUM(val)
FROM (
SELECT NVL(fees, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
FROM dual
CONNECT BY
level <= 10000
)) as fees
FROM Students
--COALESCE Example Code
-- COALESCE
SELECT student_id, student_name, (SELECT SUM(val)
FROM (
SELECT COALESCE(fees, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
FROM dual
CONNECT BY
level <= 10000
)) as fees
FROM Students
--NVL Code took a lot of time to fetch records from the database but the COALESCE query fetched records in considerably less time compared to NVL. It is because NVL and COALESCE have separate running mechanisms and my thinking is this kind of scenario COALESCE performs well.
I would like to add explanation plans for both cases you can see those EXPLAIN PLAN costs are equal but time not equal for both queries. It is because queues look equal but when running they have their mechanism to fetch data.
In this example, you can see Queries use FULL TABLE ACCESS to fetch data If you want you can have an Oracle index on the STUDENT table for required columns.
This is one good example of how to change Oracle COALESCE vs NVL performance. Therefore use NVL or Oracle COALESCE according to the situation. Because both are good if you use them in the correct place.
Now you can decide COALESCE to do everything that NVL can do. Is it? But in Some cases better to use NVL rather than COALESCE
- NVL – The huge benefit of NVL is no need to add both parameters in the same type.
- COALESCE – Expect all the expressions in the same datatype therefore fore you have to add all the expressions in the same data type.
--
Select nvl('world', 123) result from dual;
--Result
--world
select coalesce(‘world’, 123) result from dual;
--Result
--Error: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
--This is not related to Oracle COALESCE vs NVL performance but I thought to include this tip for your information.
Conclusion:
In conclusion, NVL is a much old function but there are a lot of benefits COALESCE doing the same thing and in addition, there are some extra benefits. If you doing some queries such as the above first 4 examples then considering performance there is not much difference with those. But if you have complex functions as a default value in NVL then that may be a concern in performance. COALESCE is good if you have a lot of possible columns as your expressions.
Reference
