Hi,

You may need to tune your database settings to get the index to be used.

An index where 90% of the records has the same value (NULL, Status=Closed,
...) may not be used otherwise, as the database may determine that it is
not selective enough. And it would not be selective enough if you searched
for NULL/Closed, but when any of the other values are referenced, it could
improve performance considerably.

        Best Regards - Misi, RRR AB, http://www.rrr.se

Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10):
* RRR|License - Not enough Remedy licenses? Save money by optimizing.
* RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs.
Find these products, and many free tools and utilities, at http://rrr.se.

> 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:[email protected]] On Behalf Of Frank Caruso
> Sent: Monday, October 25, 2010 8:33 AM
> To: [email protected]
> 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
> <[email protected]<mailto:[email protected]>> wrote:
> Why do you need an index on a column that rarely contains a value?
>
> --
> J
>
> 2010/10/22 Frank Caruso
> <[email protected]<mailto:[email protected]>>:
>> ** 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"
>

_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"

Reply via email to