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.

Order of columns in an Index

2 posts in General Discussion Last posting was on 2011-12-30 16:01:00.0Z
RGS Posted on 2011-12-30 14:54:45.0Z
Sender: 383a.4efdcf4b.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Order of columns in an Index
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4efdd0b5.3893.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 30 Dec 2011 06:54:45 -0800
X-Trace: forums-1-dub 1325256885 172.20.134.41 (30 Dec 2011 06:54:45 -0800)
X-Original-Trace: 30 Dec 2011 06:54:45 -0800, 172.20.134.41
Lines: 16
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30794
Article PK: 73684

If a have a nonclustered index like that:

index_1 on table_example (c1,c2,c3)

table_example has a DOL lock

If I try to execute:

select * from table_example where c2= @2 and c3 = @3

Will ASE use the index index_1? Is neccesary or not a where
clause with the field c1 for use the index?
If ASE use the index, how can it be possible if in the
sentence we don't have value por c1 field?

Thanks!


"Mark A. Parsons" <iron_horse Posted on 2011-12-30 16:01:00.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Order of columns in an Index
References: <4efdd0b5.3893.1681692777@sybase.com>
In-Reply-To: <4efdd0b5.3893.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4efde03c@forums-1-dub>
Date: 30 Dec 2011 08:01:00 -0800
X-Trace: forums-1-dub 1325260860 10.22.241.152 (30 Dec 2011 08:01:00 -0800)
X-Original-Trace: 30 Dec 2011 08:01:00 -0800, vip152.sybase.com
Lines: 33
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30795
Article PK: 73685

What does the query plan show when you actually run the query?

----------

Will the optimizer choose to use the index? yes, no, maybe ... it depends ...

- if you have up-to-date stats on all indexed columns
- and the c2/c3 combo represents a (relatively) small number of rows in the table
- and the size of the index is (relatively) small compared to the size of the table
- and assuming no other usable index

... then the optimizer may choose an index scan as being cheaper than a table scan (ie, fewer IOs to scan the index as
opposed to scanning table).

On 12/30/2011 09:54, RGS wrote:
> If a have a nonclustered index like that:
>
> index_1 on table_example (c1,c2,c3)
>
> table_example has a DOL lock
>
> If I try to execute:
>
> select * from table_example where c2= @2 and c3 = @3
>
> Will ASE use the index index_1? Is neccesary or not a where
> clause with the field c1 for use the index?
> If ASE use the index, how can it be possible if in the
> sentence we don't have value por c1 field?
>
> Thanks!