473,837 Members | 1,766 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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.

Feb 5 '07 #1
8 5593
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
Feb 5 '07 #2
"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
Feb 5 '07 #3


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

Feb 5 '07 #4
"shira" <sh********@yah oo.comwrote in message
news:11******** **************@ a75g2000cwd.goo glegroups.com.. .
>
Didn't you read Albert's response?

Keith.
Feb 5 '07 #5
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

Feb 5 '07 #6
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" <h...@there.com wrote:
"shira" <shirana...@yah oo.comwrote in message

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

Didn't you read Albert's response?

Keith.

Feb 5 '07 #7
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

Feb 6 '07 #8
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, u...@hotmail.co 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

Feb 6 '07 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
2086
by: kmunderwood | last post by:
I am trying to combine "if match=" and "when test" I am a newbie, and have made both work separately, but I can not seem to combine them. This is my xml("index.xml")page(I can not change this, it comes to me this way. <?xml version="1.0" encoding="iso-8859-1" ?>
1
2375
by: Stephane Charette | last post by:
QUICK VERSION: How do I create an index on a field of type "MONEY"? ----------------------------- LONG VERSION: I have a table with a field of type "money". I very often need to access records by the purchase price so I thought I'd create an index to help out my selects: CREATE INDEX purchasepriceidx ON mytable (purchaseprice);
3
22650
by: Prince Kumar | last post by:
Is there any way I can define an Unique constraint or unique index which allows more than one null values for the same column combination in DB2? ie, If my index is defined on (col3, col4) where both columns allow nulls, I want col3 + col4 to be unique, if one or both the columns have values. If both columns have nulls, it should allow more than one such rows. ex,
12
4790
by: Prabu Subroto | last post by:
Dear my friends... I am using postgres 7.4 and SuSE 9.1. I want to use auto_increment as on MySQL. I look up the documentation on www.postgres.com and I found "serial" . But I don't know how to create auto_increment. here is my try:
12
13932
by: Emi Lu | last post by:
Hello all, I have a question about "date" & "timestamp" types in PostgreSQL. I want to setup the default value '0000-00-00' and "0000-00-00 00:00:00" for them. However, it seems that PostgreSQL does not support it. Could someone helps me please? The example table: T1 (col1 varchar(7) not null,
77
4046
by: M.B | last post by:
Guys, Need some of your opinion on an oft beaten track We have an option of using "goto" in C language, but most testbooks (even K&R) advice against use of it. My personal experience was that goto sometimes makes program some more cleaner and easy to understand and also quite useful (in error handling cases). So why goto is outlawed from civilized c programmers community. is there any technical inefficiency in that.
4
1578
by: psyconia | last post by:
Hi, I am trying to run a query which whill pull the data from fields if the are "not empty" however what happens is if any of the records are empty then it does not show that record. I have tried setting each text field criteria to "not null" but this doesn't pull anything out because all of the records are missing a few of these text fields. Any ideas ? Thanx...
20
12462
by: chutsu | last post by:
I'm trying to compare between pointer and integer in an "IF" statement how do I make this work? if(patient.id != NULL){ } Thanks Chris
6
2336
by: Mathieu Prevot | last post by:
Hi there, it seems that child.wait() is ignored when print "Server running "%(child.pid) fpid.write(child.pid) are between the process creation child = Popen(cmd.split(), stderr=flog) and child.wait(). It seems to be a bug, doesn't it ?
0
9693
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10583
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10280
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9419
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7823
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7009
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5679
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4056
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3128
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.