| Index with NVL [message #483222] |
Fri, 19 November 2010 05:07  |
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  |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
