Unanswered: Optimising Null Check in a Query. Need Help in Altering Table or a Better Solution.
For a table:
Id , not null.
first_name, not null.
last_call date, nullable.
Now i need to check for all Id whose last_call_date is not null. and i have about 3.some million records and obviously the query hangs up pretty bad.
I need a better design for this table or a better query to make this work.
The actual query looks similar to this:
select count(t1.Id), t2.filter_name ...... from table t1, table t2,....
where t2.filter_id == t3.filter_id
and t1.last_call_date is null.
Things that i have already considered.
1. Make an index on last_call_date , but since it is a nullable column the overhead would be very high. Also i am planning to update the tables using batch scripts with record counts reaching some millions.
2. Alter the table to create a new column ( as stupid as it sounds ) which would be not nullable and would have a distinct value for all null values of last_call_date ( hence create an index over that ), which would be the last resort actually, but that would really change the system big time.
3. Used a lot of techniques , ie rephrased the query , tried decode, nvl etc..
And now i am stuck. AnyHelp would be gratefully appreciated.
Thanks a lot.
wow that's a new find.
i'll try that out... i came so close to this solution.
Since the number of records are very high in PROD ( 4-5 billion records)... i need the query to take atmost .5 of seconds, since it would be called in the weblayer.