472,368 Members | 2,496 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,368 software developers and data experts.

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 5358
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********@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
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********@yahoo.comwrote in message
news:11**********************@a75g2000cwd.googlegr oups.com...
>
Didn't you read Albert's response?

Keith.
Feb 5 '07 #5
On Feb 5, 11:16 am, "shira" <shirana...@yahoo.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.comwrote:
"shira" <shirana...@yahoo.comwrote in message

news:11**********************@a75g2000cwd.googlegr oups.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.com 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
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,...
1
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...
3
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...
12
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...
12
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...
77
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...
4
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...
20
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
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...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
2
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
1
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
1
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
0
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.

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.