Index with NVL [message #483222] |
Fri, 19 November 2010 05:07 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ss1_3
Messages: 4 Registered: December 2009
|
Junior Member |
|
|
I have a query which had a join:
a.c1=b.c1 and a.c2=@var
where @var is user supplied input at runtime
We had a index on a.c2 . The CBO would use this index to generate an opitimised query plan.
We found some records from table "b" were dropping due to inner join. So we made a change in join. It'd be like
a.c1(+)=b.c1 and nvl(a.c2,@var)=@var
This query is no longer using the index, instead its doing a full table scan causing the query to slowdown.
I have tried creating index on
nvl(a.c2,'31-dec-9999')
But the CBO won't use it.Anyway to create index on this col so that full table scan can be avoided?
Thanks
|
|
|
|
Re: Index with NVL [message #483244 is a reply to message #483241] |
Fri, 19 November 2010 08:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68678 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:I have tried creating index on
nvl(a.c2,'31-dec-9999')
But the CBO won't use it.
First, because '31-dec-9999' is not a date but a string.
Quote:Anyway to create index on this col so that full table scan can be avoided?
Why do you think FTS is bad?
Regards
Michel
|
|
|