"shira" <sh********@yahoo.comwrote in message
news:11**********************@v33g2000cwv.googlegr oups.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