Question about NULL
HI All,
I have a table that uses the following query which searches for any open record
that is not assigned to me.
'Status' Closed AND ('Assigned Person' != Mark Brittain OR 'Assigned
Person' = NULL)
If I use the query without the Assigned Person' = NULL the result does not
include any records that are not assigned. Both fields are indexed but the
NULL causes the full table scan. Is there a way to write query so that
indexing is used but I get the records that are unassigned?
Thanks
Mark
Mark Brittain
Remedy Developer
NaviSite
mbritt...@navisite.commailto:mbritt...@navisite.com
(315) 453-2912 x5335 (Office)
(315) 317.2897 (Cell)
This e-mail is the property of NaviSite, Inc. It is intended only for the
person or entity to which it is addressed and may contain information that is
privileged, confidential, or otherwise protected from disclosure. Distribution
or copying of this e-mail, or the information contained herein, to anyone other
than the intended recipient is prohibited.
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Question about NULL
Just a quick question Mark...
Did you mean $NULL$ OR NULL.
On Mar 14, 2:05 pm, Brittain, Mark mbritt...@navisite.com wrote:
HI All,
I have a table that uses the following query which searches for any open
record that is not assigned to me.
'Status' Closed AND ('Assigned Person' != Mark Brittain OR 'Assigned
Person' = NULL)
If I use the query without the Assigned Person' = NULL the result does not
include any records that are not assigned. Both fields are indexed but the
NULL causes the full table scan. Is there a way to write query so that
indexing is used but I get the records that are unassigned?
Thanks
Mark
Mark Brittain
Remedy Developer
NaviSite
mbritt...@navisite.commailto:mbritt...@navisite.com
(315) 453-2912 x5335 (Office)
(315) 317.2897 (Cell)
This e-mail is the property of NaviSite, Inc. It is intended only for the
person or entity to which it is addressed and may contain information that is
privileged, confidential, or otherwise protected from disclosure.
Distribution or copying of this e-mail, or the information contained herein,
to anyone other than the intended recipient is prohibited.
___
UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org
attend wwrug11www.wwrug.comARSList: Where the Answers Are
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Question about NULL
$NULL$
'Status' Closed AND ('Assigned Person' != Mark Brittain OR 'Assigned
Person' = $NULL$)
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Elry
Sent: Monday, March 14, 2011 2:40 PM
To: arslist@ARSLIST.ORG
Subject: Re: Question about NULL
Just a quick question Mark...
Did you mean $NULL$ OR NULL.
On Mar 14, 2:05 pm, Brittain, Mark mbritt...@navisite.com wrote:
HI All,
I have a table that uses the following query which searches for any open
record that is not assigned to me.
'Status' Closed AND ('Assigned Person' != Mark Brittain OR 'Assigned
Person' = NULL)
If I use the query without the Assigned Person' = NULL the result does not
include any records that are not assigned. Both fields are indexed but the
NULL causes the full table scan. Is there a way to write query so that
indexing is used but I get the records that are unassigned?
Thanks
Mark
Mark Brittain
Remedy Developer
NaviSite
mbritt...@navisite.commailto:mbritt...@navisite.com
(315) 453-2912 x5335 (Office)
(315) 317.2897 (Cell)
This e-mail is the property of NaviSite, Inc. It is intended only for the
person or entity to which it is addressed and may contain information that is
privileged, confidential, or otherwise protected from disclosure.
Distribution or copying of this e-mail, or the information contained herein,
to anyone other than the intended recipient is prohibited.
___
UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org
attend wwrug11www.wwrug.comARSList: 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
Re: Question about NULL
The clause ('Assigned Person' != Mark Brittain) will not use an index since
it is a not equal, so the only Index you could use is one on Status. I was
told (a long time ago) it is better to use = instead of (supposedly = helps
the database use an index).
Can you look and see when the last time that the index on Status was analyzed?
Fred
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Brittain, Mark
Sent: Monday, March 14, 2011 1:49 PM
To: arslist@ARSLIST.ORG
Subject: Re: Question about NULL
$NULL$
'Status' Closed AND ('Assigned Person' != Mark Brittain OR 'Assigned
Person' = $NULL$)
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Elry
Sent: Monday, March 14, 2011 2:40 PM
To: arslist@ARSLIST.ORG
Subject: Re: Question about NULL
Just a quick question Mark...
Did you mean $NULL$ OR NULL.
On Mar 14, 2:05 pm, Brittain, Mark mbritt...@navisite.com wrote:
HI All,
I have a table that uses the following query which searches for any open
record that is not assigned to me.
'Status' Closed AND ('Assigned Person' != Mark Brittain OR 'Assigned
Person' = NULL)
If I use the query without the Assigned Person' = NULL the result does not
include any records that are not assigned. Both fields are indexed but the
NULL causes the full table scan. Is there a way to write query so that
indexing is used but I get the records that are unassigned?
Thanks
Mark
Mark Brittain
Remedy Developer
NaviSite
mbritt...@navisite.commailto:mbritt...@navisite.com
(315) 453-2912 x5335 (Office)
(315) 317.2897 (Cell)
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Question about NULL
Mark,
First, about why your search is not working as you think it should have.
NULL is a strange beast in databases. It is a value. A very special value
that means there is no value. The
only way to find things that have this special no value value is to
explicitly look for it. No value does not
match a != comparison.
This is why you have to explicitly check for it in your qualification to find
things that are unassigned.
Now, about indexing. It actually is not the NULL test that is causing the
table scan. If you have a field
indexed and you search for NULL, you will use the index.
The thing that is causing the table scan is the != Mark Brittain clause. A
!= will cause the system to
perform a table scan as there is nothing to index on.
To find all the unassigned records using the index, just use the clause
'Assigned Person' = $NULL$
This will use the index on Assigned Person and it will find all unassigned
records.
I am not sure what the != Mark Brittain clause is for if you are looking for
unassigned records. That will
find ASSIGNED records that are not assigned to Mark. So, that search is really
looking for all records
not assigned to anyone (the NULL clause) and those not assigned to Mark. If
you can remove the != Mark
from your criteria, you will find things much more efficient.
Now, all this is assuming you don't qualify on Status too. If there is an
index on Status, it should help with
the search and that index can be used. However, the algorithm for determining
whether to use an index may
be determining that there is a large percentage match in the table so it
defaults to a table scan rather than
trying to use the index on Status.
I hope this helps,
Doug Mueller
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Brittain, Mark
Sent: Monday, March 14, 2011 11:06 AM
To: arslist@ARSLIST.ORG
Subject: Question about NULL
**
HI All,
I have a table that uses the following query which searches for any open record
that is not assigned to me.
'Status' Closed AND ('Assigned Person' != Mark Brittain OR 'Assigned
Person' = NULL)
If I use the query without the Assigned Person' = NULL the result does not
include any records that are not assigned. Both fields are indexed but the NULL
causes the full table scan. Is there a way to write query so that indexing is
used but I get the records that are unassigned?
Thanks
Mark
Mark Brittain
Remedy Developer
NaviSite
mbritt...@navisite.commailto:mbritt...@navisite.com
(315) 453-2912 x5335 (Office)
(315) 317.2897 (Cell)
This e-mail is the property of NaviSite, Inc. It is intended only for the
person or entity to which it is addressed and may contain information that is
privileged, confidential, or otherwise protected from disclosure. Distribution
or copying of this e-mail, or the information contained herein, to anyone other
than the intended recipient is prohibited.
_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
Re: Question about NULL
Hi Doug,
Great explanation and I appreciate the time taken to reply to the issue. On
the display only form I have two table fields. The first looks for open records
assigned to me and the second looks for open records not assigned to me. This
is why I am using the != Mark clause.
Thanks for the advice.
Mark
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Mueller, Doug
Sent: Monday, March 14, 2011 3:07 PM
To: arslist@ARSLIST.ORG
Subject: Re: Question about NULL
**
Mark,
First, about why your search is not working as you think it should have.
NULL is a strange beast in databases. It is a value. A very special value
that means there is no value. The
only way to find things that have this special no value value is to
explicitly look for it. No value does not
match a != comparison.
This is why you have to explicitly check for it in your qualification to find
things that are unassigned.
Now, about indexing. It actually is not the NULL test that is causing the
table scan. If you have a field
indexed and you search for NULL, you will use the index.
The thing that is causing the table scan is the != Mark Brittain clause. A
!= will cause the system to
perform a table scan as there is nothing to index on.
To find all the unassigned records using the index, just use the clause
'Assigned Person' = $NULL$
This will use the index on Assigned Person and it will find all unassigned
records.
I am not sure what the != Mark Brittain clause is for if you are looking for
unassigned records. That will
find ASSIGNED records that are not assigned to Mark. So, that search is really
looking for all records
not assigned to anyone (the NULL clause) and those not assigned to Mark. If
you can remove the != Mark
from your criteria, you will find things much more efficient.
Now, all this is assuming you don't qualify on Status too. If there is an
index on Status, it should help with
the search and that index can be used. However, the algorithm for determining
whether to use an index may
be determining that there is a large percentage match in the table so it
defaults to a table scan rather than
trying to use the index on Status.
I hope this helps,
Doug Mueller
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Brittain, Mark
Sent: Monday, March 14, 2011 11:06 AM
To: arslist@ARSLIST.ORG
Subject: Question about NULL
**
HI All,
I have a table that uses the following query which searches for any open record
that is not assigned to me.
'Status' Closed AND ('Assigned Person' != Mark Brittain OR 'Assigned
Person' = NULL)
If I use the query without the Assigned Person' = NULL the result does not
include any records that are not assigned. Both fields are indexed but the NULL
causes the full table scan. Is there a way to write query so that indexing is
used but I get the records that are unassigned?
Thanks
Mark
Mark Brittain
Remedy Developer
NaviSite
mbritt...@navisite.commailto:mbritt...@navisite.com
(315) 453-2912 x5335 (Office)
(315) 317.2897 (Cell)
This e-mail is the property of NaviSite, Inc. It is intended only for the
person or entity to which it is addressed and may contain information that is
privileged, confidential, or otherwise protected from disclosure. Distribution
or copying of this e-mail, or the information contained herein, to anyone other
than the intended recipient is prohibited.
_attend WWRUG11 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
Question about NULL
I suspect the key question that hasn't been asked is, what database are you using? Getting onto my soap box, Oracle (the last time I checked) is completely clueless to the meaning of null. ie == NULL as far as Oracle is concerned, which may have been correct in 1985, but isn't correct now we're trying to map data (where NULL exists) to tables. John Baker -- Single Sign On for AR System http://www.javasystemsolutions.com/jss/ssoplugin ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Question about NULL
I don't think so MarkI believe the reason you are causing the table scan
is because of the OR, not because of the $NULL$. You may try a compound
index on Status and Assigned Personthis MAY help avoid a table scan, but
you must include the $NULL$ to get those...let me know if a compound index
works for this.
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Brittain, Mark
Sent: Monday, March 14, 2011 12:49 PM
To: arslist@ARSLIST.ORG
Subject: Re: Question about NULL
$NULL$
'Status' Closed AND ('Assigned Person' != Mark Brittain OR 'Assigned
Person' = $NULL$)
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Elry
Sent: Monday, March 14, 2011 2:40 PM
To: arslist@ARSLIST.ORG
Subject: Re: Question about NULL
Just a quick question Mark...
Did you mean $NULL$ OR NULL.
On Mar 14, 2:05 pm, Brittain, Mark mbritt...@navisite.com wrote:
HI All,
I have a table that uses the following query which searches for any open
record that is not assigned to me.
'Status' Closed AND ('Assigned Person' != Mark Brittain OR 'Assigned
Person' = NULL)
If I use the query without the Assigned Person' = NULL the result does not
include any records that are not assigned. Both fields are indexed but the
NULL causes the full table scan. Is there a way to write query so that
indexing is used but I get the records that are unassigned?
Thanks
Mark
Mark Brittain
Remedy Developer
NaviSite
mbritt...@navisite.commailto:mbritt...@navisite.com
(315) 453-2912 x5335 (Office)
(315) 317.2897 (Cell)
This e-mail is the property of NaviSite, Inc. It is intended only for the
person or entity to which it is addressed and may contain information that
is privileged, confidential, or otherwise protected from disclosure.
Distribution or copying of this e-mail, or the information contained herein,
to anyone other than the intended recipient is prohibited.
___
UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org
attend wwrug11www.wwrug.comARSList: 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
Question about NULL
How about:
'Status' Closed AND NOT ('Assigned Person' == Mark Brittain)
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Question about NULL
DOH!!didn't catch the !=...yea...that certainly won't use an index.
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W
Sent: Monday, March 14, 2011 12:56 PM
To: arslist@ARSLIST.ORG
Subject: Re: Question about NULL
The clause ('Assigned Person' != Mark Brittain) will not use an index
since it is a not equal, so the only Index you could use is one on Status.
I was told (a long time ago) it is better to use = instead of (supposedly
= helps the database use an index).
Can you look and see when the last time that the index on Status was
analyzed?
Fred
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Brittain, Mark
Sent: Monday, March 14, 2011 1:49 PM
To: arslist@ARSLIST.ORG
Subject: Re: Question about NULL
$NULL$
'Status' Closed AND ('Assigned Person' != Mark Brittain OR 'Assigned
Person' = $NULL$)
-Original Message-
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Elry
Sent: Monday, March 14, 2011 2:40 PM
To: arslist@ARSLIST.ORG
Subject: Re: Question about NULL
Just a quick question Mark...
Did you mean $NULL$ OR NULL.
On Mar 14, 2:05 pm, Brittain, Mark mbritt...@navisite.com wrote:
HI All,
I have a table that uses the following query which searches for any open
record that is not assigned to me.
'Status' Closed AND ('Assigned Person' != Mark Brittain OR 'Assigned
Person' = NULL)
If I use the query without the Assigned Person' = NULL the result does not
include any records that are not assigned. Both fields are indexed but the
NULL causes the full table scan. Is there a way to write query so that
indexing is used but I get the records that are unassigned?
Thanks
Mark
Mark Brittain
Remedy Developer
NaviSite
mbritt...@navisite.commailto:mbritt...@navisite.com
(315) 453-2912 x5335 (Office)
(315) 317.2897 (Cell)
___
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
Re: Question about NULL
Swapping the Assigned person test around will also improve the query -
the order of the query tests does matter not matter what DB is being
used
'Status' Closed AND ('Assigned Person' = $NULL$ OR NOT ('Assigned
Person' == Mark Brittain))
How about:
'Status' Closed AND NOT ('Assigned Person' == Mark Brittain)
___
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
