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.

select Query with where clause

3 posts in General Discussion Last posting was on 2010-09-16 13:44:37.0Z
YSR Posted on 2010-09-13 23:47:49.0Z
Sender: 165f.4c8eb4fd.1804289383@sybase.com
From: YSR
Newsgroups: sybase.public.ase.general
Subject: select Query with where clause
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c8eb825.1683.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 13 Sep 2010 16:47:49 -0700
X-Trace: forums-1-dub 1284421669 10.22.241.41 (13 Sep 2010 16:47:49 -0700)
X-Original-Trace: 13 Sep 2010 16:47:49 -0700, 10.22.241.41
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29558
Article PK: 78788

Hi All,

I am not sure this might have posted alsredy....

1) I have run the below Query

select * from employee where salary >10000
go

the employee table have no index on it..., here my doubt is
when we run the select stmnt on the "employee' table will
this create a #temp table on tempdb or it will directly scan
the table from with out creating the #tmptable and popup the
output to client.


2) the same query(above) have the index.... how it differ
from first one?

Thanks in advance....


Michael Peppler [Team Sybase] Posted on 2010-09-14 04:48:35.0Z
From: "Michael Peppler [Team Sybase]" <mpeppler@peppler.org>
Organization: Peppler Consulting SARL
Subject: Re: select Query with where clause
User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing moment of clarity.)
Message-ID: <pan.2010.09.14.04.48.35.295234@peppler.org>
Newsgroups: sybase.public.ase.general
References: <4c8eb825.1683.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 13 Sep 2010 21:48:35 -0700
X-Trace: forums-1-dub 1284439715 10.22.241.152 (13 Sep 2010 21:48:35 -0700)
X-Original-Trace: 13 Sep 2010 21:48:35 -0700, vip152.sybase.com
Lines: 30
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29559
Article PK: 78791


On Mon, 13 Sep 2010 16:47:49 -0700, YSR wrote:

> Hi All,
>
> I am not sure this might have posted alsredy....
>
> 1) I have run the below Query
>
> select * from employee where salary >10000
> go
>
> the employee table have no index on it..., here my doubt is
> when we run the select stmnt on the "employee' table will
> this create a #temp table on tempdb or it will directly scan
> the table from with out creating the #tmptable and popup the
> output to client.

If there is no ORDER BY clause, or any other reason for sorting the data,
then no worktable is created, and the table is simply scanned once and the
data matching the where clause is returned to the client when it is found.


> 2) the same query(above) have the index.... how it differ
> from first one?

It doesn't - except that the optimizer will use the index to find the
matching rows.

Michael


YSR Posted on 2010-09-16 13:44:37.0Z
Sender: 5551.4c921e51.1804289383@sybase.com
From: YSR
Newsgroups: sybase.public.ase.general
Subject: Re: select Query with where clause
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c921f45.5565.1681692777@sybase.com>
References: <pan.2010.09.14.04.48.35.295234@peppler.org>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Sep 2010 06:44:37 -0700
X-Trace: forums-1-dub 1284644677 10.22.241.41 (16 Sep 2010 06:44:37 -0700)
X-Original-Trace: 16 Sep 2010 06:44:37 -0700, 10.22.241.41
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29567
Article PK: 78798


> On Mon, 13 Sep 2010 16:47:49 -0700, YSR wrote:
>
> > Hi All,
> >
> > I am not sure this might have posted alsredy....
> >
> > 1) I have run the below Query
> >
> > select * from employee where salary >10000
> > go
> >
> > the employee table have no index on it..., here my doubt
> > is when we run the select stmnt on the "employee' table
> > will this create a #temp table on tempdb or it will
> > directly scan the table from with out creating the
> > #tmptable and popup the output to client.
>
> If there is no ORDER BY clause, or any other reason for
> sorting the data, then no worktable is created, and the
> table is simply scanned once and the data matching the
> where clause is returned to the client when it is found.
>
>
> > 2) the same query(above) have the index.... how it
> > differ from first one?
>
> It doesn't - except that the optimizer will use the index
> to find the matching rows.
>
> Michael

Thanks Michael......