Search

Top 60 Oracle Blogs

Recent comments

coalesce

NVL vs COALESCE

Jonathan Lewis just published a blog post about NVL and COALESCE and the optimizer costings for each.

There is also perhaps a significant difference between NVL and COALESCE in that the former seems to have an in-built optimization for handling bind variables and nulls. 

Consider an application where users optionally pass in search criteria and you have to query a table based on that criteria.  You have three natural choices here to implement that:

WHERE column = :search_criteria or :search_criteria is null

or

WHERE column = nvl(:search_criteria ,column)

or

WHERE column = coalesce(:search_criteria,column)

Functionally they are identical*, but the implementation detail shows a nice little optimizer trick that only works with NVL.

Index ITL

Jonathan Lewis wrote a series of posts on topic of Index ITLs (interested transaction list), how and when they become huge and make index bigger in size – see this post with links to several other articles. As it turned out in a recent thread on SQL.ru forum, one of possible solutions to the issue [...]