Frank, The overhead is the same for the index regardless of content. Why? Because NULL is a value (actually the lack of a value) and that has to be updated in the index too so that you can efficiently search for NULL values just like any other value.
So, the cost of the index is really the same. The answer for you about whether to add it is whether it is important for retrieval performance. From what you describe, it is. Without it, you will get table scans in the use case you indicate and if it is a large table, that is a challenge. In general, the overhead for maintaining an index is reasonably small and if there is a significant use for that index for widespread or even narrow use patterns, it is worth creating. Doug Mueller ________________________________ From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Frank Caruso Sent: Monday, October 25, 2010 8:33 AM To: arslist@ARSLIST.ORG Subject: Re: Indexing Values Rarely Used ** Because they want to be able to search on that field with no other criteria. Its sort of like a pointer to an end user that may or may be included in a ticket. Anyhow, I was just hoping that adding the index would not put any extra strain on the database because the value would not often be included. On Mon, Oct 25, 2010 at 10:14 AM, Jarl Grøneng <jarl.gron...@gmail.com<mailto:jarl.gron...@gmail.com>> wrote: Why do you need an index on a column that rarely contains a value? -- J 2010/10/22 Frank Caruso <caruso.fr...@gmail.com<mailto:caruso.fr...@gmail.com>>: > ** Oracle 10G on Solaris 10 > > If in an index is added to a form against a single column but that column > rarely contains a value, does it have the same impact on the database as a > column that is indexed against a required field? > > My thought is that if there is no value provided for the column then the > database does not perform any extra work to maintain the index. > > Thank you > > Frank > _attend WWRUG10 www.wwrug.com<http://www.wwrug.com> ARSlist: "Where the > Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org<http://www.arslist.org> attend wwrug11 www.wwrug.com<http://www.wwrug.com> ARSList: "Where the Answers Are" _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
