0

MY query like below

select  nvl(a.value1,nvl(b.value1,nvl(c.value1,''))
 from table1 a, table2 b table3 c 
where a.value2=b.value2 and b.value3=c.value3

As this query contains a nvl function which tables the value1 from three of my tables it is making a full table scan , i understand if i can make a function based index oracle should consider that index, but my concern is as the nvl function is on columns of three different tables i am unable to craete function based index, any otherway to rewrite the query or any otherway i can use index here, help please

Popeye
  • 35,427
  • 4
  • 10
  • 31
RUDRA PARIDA
  • 1
  • 3
    Are you sure the nvl is causing your problem - you aren't using it in a join condition so it seems odd that index/FTS is affected? Incidentally, `nvl(c.value1,'')` is pointless as `''` is the same as null in Oracle; and you should consider using modern join syntax. – Alex Poole Jul 01 '20 at 19:21

2 Answers2

0

You can use coalesce function as follows:

Coalesce(a.value1, b.value1, c.value1)

Or you can use the case when statement as follows:

Case when a.value1 is not null then a.valie1
     When b.value1 is not null then b.value1
     Else c.value1
End

Note: performance of coalesce is better than the NVL, as coleasce uses the short-circuit evaluation means it evaluates the arguments only if they are needed.

Popeye
  • 35,427
  • 4
  • 10
  • 31
0

It's not really possible to make a definitive comment on a query without seeing an execution plan. I don't think you can create a function-based index on multiple tables simultaneously. Honestly, I doubt the NVL is much of a bottleneck. What you might do is make three indexes:

create index idx1 on table1( value2, value1 );
create index idx2 on table2( value3, value2, value1 );
create index idx3 on table3( value3, value1 );

At least then, Oracle won't have to get the full row for each table.

eaolson
  • 14,717
  • 7
  • 43
  • 58