Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Would it serve any benefit to Index a Nullable Column?

User_OMEF8Apr 1 2014 — edited Apr 2 2014

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.

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 30 2014
Added on Apr 1 2014
14 comments
5,106 views