Query runs slow when NVL is used in Oracle

Question

I have a view when I query this view it runs slow. However if I remove NVL function it is running fast.

I have checked execution plan, when NVL function is used it is not using any index. If NVL function is not used it is index. I tried to specify the index as hint but it did not help. Not sure whether the hint is considered or not while executing.

Below is the view and the query used to create the view.

CREATE OR REPLACE FORCE VIEW  "test"(a,b)
select NVL(table1.a,table2.a) a,table2.b from table1 join table2 on table1.id=table2.id 

The query that I am using on view is

select * from test where a='XYZ'

Any thoughts what should I do to make above query run fast?

Answer 1

Not sure if you got an answer to this already - You would need to use a Functional based index using NVL(a). By default Oracle will not index null columns, so when you query using NVL, it will perform a full table scan.