Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Does an index uses null values

3 posts in General Discussion Last posting was on 2010-03-05 19:44:01.0Z
A123 Posted on 2010-03-04 22:30:48.0Z
Sender: 1067.4b9033a3.1804289383@sybase.com
From: A123
Newsgroups: sybase.public.ase.general
Subject: Does an index uses null values
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b903498.1073.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 4 Mar 2010 14:30:48 -0800
X-Trace: forums-1-dub 1267741848 10.22.241.41 (4 Mar 2010 14:30:48 -0800)
X-Original-Trace: 4 Mar 2010 14:30:48 -0800, 10.22.241.41
Lines: 8
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29028
Article PK: 78267

Can somebody verify that ase 15 is using the index to look
for null values?
Assume I have a table of 10 million rows and 50,000 of them
are null and there is an index on that column. If I select
count(*) from that column where is null, will ase use the
index or do a table scan?

Thanks


"Mark A. Parsons" <iron_horse Posted on 2010-03-04 22:56:54.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Does an index uses null values
References: <4b903498.1073.1681692777@sybase.com>
In-Reply-To: <4b903498.1073.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 100301-0, 03/01/2010), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b903ab6$1@forums-1-dub>
Date: 4 Mar 2010 14:56:54 -0800
X-Trace: forums-1-dub 1267743414 10.22.241.152 (4 Mar 2010 14:56:54 -0800)
X-Original-Trace: 4 Mar 2010 14:56:54 -0800, vip152.sybase.com
Lines: 21
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29029
Article PK: 78266

Try running the following:

=======================
set showplan on
go
select count(*) from <table> where <column> is NULL
go
=======================

The query plan will tell you if the optimizer has chosen to access the table via an index or table scan.

A123 wrote:
> Can somebody verify that ase 15 is using the index to look
> for null values?
> Assume I have a table of 10 million rows and 50,000 of them
> are null and there is an index on that column. If I select
> count(*) from that column where is null, will ase use the
> index or do a table scan?
>
> Thanks


X123 Posted on 2010-03-05 19:44:01.0Z
Sender: 1067.4b9033a3.1804289383@sybase.com
From: X123
Newsgroups: sybase.public.ase.general
Subject: Re: Does an index uses null values
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b915f01.30af.1681692777@sybase.com>
References: <4b903ab6$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 5 Mar 2010 11:44:01 -0800
X-Trace: forums-1-dub 1267818241 10.22.241.41 (5 Mar 2010 11:44:01 -0800)
X-Original-Trace: 5 Mar 2010 11:44:01 -0800, 10.22.241.41
Lines: 25
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29031
Article PK: 78270

It does use it.
thanks

> Try running the following:
>
> =======================
> set showplan on
> go
> select count(*) from <table> where <column> is NULL
> go
> =======================
>
> The query plan will tell you if the optimizer has chosen
> to access the table via an index or table scan.
>
>
> A123 wrote:
> > Can somebody verify that ase 15 is using the index to
> > look for null values?
> > Assume I have a table of 10 million rows and 50,000 of
> > them are null and there is an index on that column. If I
> > select count(*) from that column where is null, will ase
> > use the index or do a table scan?
> >
> > Thanks