-
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..
Code:
select
nvl(in_flag, 'NULL') in_flag,
count(*)
FROM
table_name
GROUP BY
nvl(in_flag, 'NULL')
Rgds
Abhay.
funky...
"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"
-
Problem solved.
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.
Rgds
Parag
-
Hi Parag
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 :-)
regards
Hrishy
-
Hrishi,
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.
Rgds
Parag
-
Hi Parag
Thanks for the feedback i appreciate it very much.
regards
Hrishy
-
===
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.
Tamil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|