Would it serve any benefit to Index a Nullable Column?
I hope this topic is in the correct topic thread. I apologize if it is not. Additional information...
Oracle 11.2.0.2.0
I have a table with roughly ~900,000 records (at this time) and will continue to grow over time probably at the rate of ~350,000 records in addition per year. We have a column in the table called "Comments" and this field is used by our clerks to allow them to enter in their "comments" on the record that they're working on. Hence, I would highly doubt they will ever remember what their actual comment is when they search, so the equal (=) operator would most likely not ever be used when they perform a search. In most cases (probably majority, if not all), they will do a search with a clause of "where comments is null" or "where comment is like 'blah%'". At this time, we have approximately 40% of the records with a null value for Comments and I would suspect that it will probably continue to hover around the ~40% mark going forward. Because as new records are added and as records are worked on, they will probably just offset each other.
That being said, does it make sense to still Index this column? Any help/advice is greatly appreciated! Thanks everyone.