Are you sure that an index will speed up your query?
Which percentage of you rows contain a NULL?
mike asked a good Q.. whether index will help..? yes if there is small set of recs having nulls..
Can u paste the o/p of the query..
nvl(in_flag, 'NULL') in_flag,
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
We choose to alter the table with default value as "ABC".That columns now can contain only 3 values Y,N,ABC. So problem solved now.
Thats a design change.
Did you test the other methods provided by other posters here ?
You may check out the other methods too if you have time :-)
We were having 3 options.
1. Bitmap index
2. Set column value to default
3. NVL Functional index
1. I tried bitmap select was running super fast but insertion got affected (so no help)
2. In test env only (as i dont wanted more to play with PROD) i created table of data around 100mb as mirror copy of table in PROD
he he index took more space than table. I am not ready to take risk of this on PROD
3. And final solution given by you worked and normal index solved the issue.
I think this clears everybodies doubts now.....
Hrishy thanks for simple solution for which i took long way.
Thanks for the feedback i appreciate it very much.
Oracle cannot index null values and hence queries with is and is not null cannot use an index.
Part of the statement is incorrect. Oracle will use index when "IS NOT NULL" condition is used in the WHERE clause.
Click Here to Expand Forum to Full Width