We all know about basic of nvl function in oracle and somehow a bit about coalesce. But question is when to use nvl and when to use coalesce.
Suppose in a case where you are trying to read a column value and if it is null we read some default values like 0 in case of number and for character say something like 'X'.
So if the name of the column is Salary we write nvl(Salary,0) or coalesce(Salary,0). Now it is know that coalesce is a bit slower than nvl. As coalesce can take multiple arguments but nvl can take only 2. So when it is q question of only checking one single variable then nvl is the best option. But if you need to check multiple variable then coalesce is better option.
Suppose we have 4 columns to check i.e. coalesce(Amount1, Amount2, Amount3, Amount4,0). This can also be written using case when statement, i.e. case when Amount1 is not null then Amount1 when Amount2 is not null then Amount2 when Amount3 is not null then Amount3 when Amount4 is not null then Amount4 else 0 end. In case with nvl it can be written as nvl(Amount1,nvl(Amount2,nvl(Amount3,nvl(Amount4,0)))). But using so many nvl statement is not only junky code but also can lead to performance issue. So it is good to use coalsce in case of more than one column or else nvl.
Suppose in a case where you are trying to read a column value and if it is null we read some default values like 0 in case of number and for character say something like 'X'.
So if the name of the column is Salary we write nvl(Salary,0) or coalesce(Salary,0). Now it is know that coalesce is a bit slower than nvl. As coalesce can take multiple arguments but nvl can take only 2. So when it is q question of only checking one single variable then nvl is the best option. But if you need to check multiple variable then coalesce is better option.
Suppose we have 4 columns to check i.e. coalesce(Amount1, Amount2, Amount3, Amount4,0). This can also be written using case when statement, i.e. case when Amount1 is not null then Amount1 when Amount2 is not null then Amount2 when Amount3 is not null then Amount3 when Amount4 is not null then Amount4 else 0 end. In case with nvl it can be written as nvl(Amount1,nvl(Amount2,nvl(Amount3,nvl(Amount4,0)))). But using so many nvl statement is not only junky code but also can lead to performance issue. So it is good to use coalsce in case of more than one column or else nvl.
No comments:
Post a Comment