Why would you create an index with "ignore nulls" set to yes?

[shira]

I have done a fair bit of searching, but haven't yet been able to find
an explanation as to why one would set "ignore nulls" to "yes" when
creating an index. I understand what it does (I think), but I'm
looking to understand what scenario might prompt either setting (yes
or no). Any clarity you can provide is much appreciated! Thanks kindly.

[Rick Brandt]

shira wrote:

I have done a fair bit of searching, but haven't yet been able to find
an explanation as to why one would set "ignore nulls" to "yes" when
creating an index. I understand what it does (I think), but I'm
looking to understand what scenario might prompt either setting (yes
or no). Any clarity you can provide is much appreciated! Thanks
kindly.

When there is an entry I want no duplicates, but I need to allow more than one
record without a value.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Albert D. Kallal]

"shira" <sh********@yah oo.comwrote in message
news:11******** **************@ v33g2000cwv.goo glegroups.com.. .

>I have done a fair bit of searching, but haven't yet been able to find
an explanation as to why one would set "ignore nulls" to "yes" when
creating an index. I understand what it does (I think), but I'm
looking to understand what scenario might prompt either setting (yes
or no). Any clarity you can provide is much appreciated! Thanks kindly.

great question. Lets assume we have a database with 100,000 records.
However, lets assume we need a index on the middle name. and, lets further
assume that only 1% of the records have a middle name. Hence, we only have
about 1000 middle names. We still want a HIGH speed index to search for
middle names.

However, if you understand how indexes work, they PERFORM VERY VERY VERY
VERY VERY VERY poor when you index the same value over and over. So, in the
above, we have a nice balanced b-tree index of 1000 names (and, it can be
rapid search). On the other hand, if we index null values, then you we all
have 99,000 nulls in the same index..and father, they are placed ONE AFTER
ANOTHER in the index. In other words, to search for a null, 99,000 values in
the index must be searched. (the values are THE SAME..and the index can't
distinguish one value from another...so, they much be all clumped together -
clumping together 99,000 values in a row in a index is not better then
simply searching the whole main file - the index will actually become a
hindrance).

A index system works great when he values are different, but if you shove
99,000 values into the SAME INDEX key value...then 99,000 values will be
returned when you search for a null in that field....

However, lets assume that 75% of the middle names ARE in the database, and
we tell ms-sacks to SEARCH FOR BLANK (null) names...then the index can be
put to good use.

So, the feature is there simply asks you:

Do you really think it makes sense to index null values? Why bloat a nice
high speed searching index with ONLY 1000 enters...that small index (in our
above example) is going to be BLISTERING fast. Even if the main file is 1
million records...and we only have 1000 vales in the index...it is very high
speed. On the other hand..why index millions of blank values into that
index????
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com

[shira]

Thank you, but does this setting really enforce what you're
describing? When I set the index to unique, but I set "ignore nulls"
to "no", I was still able to add multiple null values. So, it doesn't
seem like setting "ignore nulls" to "yes" would be necessary for the
situation you describe, but maybe I'm not understanding you correctly.
I'm using Access 2003.

When there is an entry I want no duplicates, but I need to allow more than one
record without a value.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Keith Wilby]

"shira" <sh********@yah oo.comwrote in message
news:11******** **************@ a75g2000cwd.goo glegroups.com.. .

>

Didn't you read Albert's response?

Keith.

[Jason Lepack]

On Feb 5, 11:16 am, "shira" <shirana...@yah oo.comwrote:

Thank you, but does this setting really enforce what you're
describing? When I set the index to unique, but I set "ignore nulls"
to "no", I was still able to add multiple null values. So, it doesn't
seem like setting "ignore nulls" to "yes" would be necessary for the
situation you describe, but maybe I'm not understanding you correctly.
I'm using Access 2003.

When there is an entry I want no duplicates, but I need to allow more than one
record without a value.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -

- Show quoted text -

An index has nothing to do with determining what data goes into a
field, but how it is retrieved.

Think of a book on Microsoft Access (it's got about 1000 pages). Now
let's say you wanted to look up information on Indexes. Think of each
word as a field. You wouldn't flip through the book one word at a
time until you found the information on indexes, that would take too
long (but that's what an unindexed field does). You would instead
flip to the index at the back of the book and find "indexes" and flip
to the pages that it mentions. (much faster). Now the index at the
back of this book is 10 pages long and includes only the words
mentioned in the book. But what if this index also catalogged every
space and piece of punctuation? There'd be a rediculous amount of
indexes for those items that nobody really cares about or would ever
want to read. Now those pieces of puntuation are like the Nulls in
Alberts description. Why would you waste time and space indexing them
if nobody wanted to look at them?

Cheers,
Jason Lepack

[shira]

Hi Keith,

Yes, I read Albert's response and it was very helpful. It just seemed
that Rick was saying something different and it wasn't consistent with
the testing I had done. Anyway, the responses I've received have been
what I needed. Thanks all.

Shira

On Feb 5, 8:41 am, "Keith Wilby" <[e-mail] wrote:

"shira" <shirana...@yah oo.comwrote in message

news:11******** **************@ a75g2000cwd.goo glegroups.com.. .

Didn't you read Albert's response?

Keith.

[umlv]

Hi all,

I found this discussion intersting.

So why then would Access offer the option of indexing null values if
it is so ilogical to do so?

When should this option be used?

thanks

David

[Jason Lepack]

The reason the option it exists is because it is not always
illogical. In Albert's example above he used the middle name
column.

Albert D. Kallal said:
"However, lets assume that 75% of the middle names ARE in the
database, and
we tell ms-sacks to SEARCH FOR BLANK (null) names...then the index can
be
put to good use."

So, if you are going to searching a field for the Nulls then it would
be wise to include them in your index.

Cheers,
Jason

On Feb 6, 1:40 pm, [e-mail] m wrote:

Hi all,

I found this discussion intersting.

So why then would Access offer the option of indexing null values if
it is so ilogical to do so?

When should this option be used?

thanks

David