One more thing Frank
Also make sure the table statistics are up to date ensure such index would be
used (at least for Oracle).
When table statistics are updated, the index statistics on that table are
updated too.
In Oracle 10g and 11g, there are scheduled jobs that keep the statistics up to
date. However, you may want to manually refresh the statistics of that table
before viewing the execution plans that would tell you whether that index was
used or not
Guillaume
________________________________________
From: Action Request System discussion list(ARSList) [arsl...@arslist.org] on
behalf of Misi Mladoniczky [...@rrr.se]
Sent: Tuesday, October 26, 2010 11:56 AM
To: arslist@ARSLIST.ORG
Subject: Re: Indexing Values Rarely Used
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: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"
>
_______________________________________________________________________________
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"
