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.

Indexes

3 posts in General Discussion Last posting was on 2010-11-05 22:28:23.0Z
RGS Posted on 2010-11-05 21:53:22.0Z
Sender: 68f8.4cd47ade.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Indexes
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4cd47cd2.6922.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 5 Nov 2010 13:53:22 -0800
X-Trace: forums-1-dub 1288994002 10.22.241.41 (5 Nov 2010 13:53:22 -0800)
X-Original-Trace: 5 Nov 2010 13:53:22 -0800, 10.22.241.41
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29683
Article PK: 78912

Hi!
I am working with Sybase ASE 15.0.3

I have a table: customer
Table has a noncluster index by fields: (id,name,age)

If I execute:

select * from customer where name = 'John'

ASE use the index? I know that the SARGS work well only if
I use the first field in the index, for example:

select * from customer where id = '1332' and name = 'John'

I don't have to create a second index by the unique feld:
"name"?

Thanks


Sherlock, Kevin [TeamSybase] Posted on 2010-11-05 22:03:32.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4cd47cd2.6922.1681692777@sybase.com>
Subject: Re: Indexes
Lines: 34
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4cd47f34$1@forums-1-dub>
Date: 5 Nov 2010 14:03:32 -0800
X-Trace: forums-1-dub 1288994612 10.22.241.152 (5 Nov 2010 14:03:32 -0800)
X-Original-Trace: 5 Nov 2010 14:03:32 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29684
Article PK: 78913

for "where name = 'John' " - most likely, no, the index as you defined it
would not be used. Also, "name" doesn't appear to be unique so your last
sentence doesn't make much sense to me.

Even if you create an index on (name) that doesn't guarantee it will be
used. What if most of your data has rows with name = 'John' ?

If you want your name search to be indexed, you have to create an index that
has "name" as the first column in the key. Will it be used? Depends on
your data.

if you create an index on (name)

<RGS> wrote in message news:4cd47cd2.6922.1681692777@sybase.com...
> Hi!
> I am working with Sybase ASE 15.0.3
>
> I have a table: customer
> Table has a noncluster index by fields: (id,name,age)
>
> If I execute:
>
> select * from customer where name = 'John'
>
> ASE use the index? I know that the SARGS work well only if
> I use the first field in the index, for example:
>
> select * from customer where id = '1332' and name = 'John'
>
> I don't have to create a second index by the unique feld:
> "name"?
>
> Thanks


"Mark A. Parsons" <iron_horse Posted on 2010-11-05 22:28:23.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: Indexes
References: <4cd47cd2.6922.1681692777@sybase.com>
In-Reply-To: <4cd47cd2.6922.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: <4cd48507$1@forums-1-dub>
Date: 5 Nov 2010 14:28:23 -0800
X-Trace: forums-1-dub 1288996103 10.22.241.152 (5 Nov 2010 14:28:23 -0800)
X-Original-Trace: 5 Nov 2010 14:28:23 -0800, vip152.sybase.com
Lines: 72
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29685
Article PK: 78914

Generally speaking, yes, for an efficient index lookup you need to supply arguments for the leading columns of the
index; alternatively create an index with leading columns that you are searching on (eg, create an index with 'name' as
the first column in the index).

The optimizer *can* still use an index when you fail to provide an argument for the leading column; a couple scenarios ...

1 - your query references only those columns that make up an index (eg, select name, age from customer where id =
'1332'); this is called index covering and the optimizer doesn't have to go visit the actual data pages

Obviously (?) there's a bit more to it than this ... if the index is actually a clustered index on a allpages locked
table then the index leaf pages *are* the data pages ... if the index is highly fragmented and requires lots of smaller
2KB IOs to scan, the optimizer may choose a table scan if it thinks using 16KB IOs would be faster ... etc

2 - if you've run 'update index statistics' on the table's indexes, and the search argument is relatively unique, then
the optimizer may choose to scan the index with the expectation that it will find relatively few matching index entries
and thus require very few accesses of the data pages

For sake of discussion ...

- assume your customer table has 100K records and resides on 20K data pages

- assume that the index on (id,name,age) resides on 5K index pages

- assume there are 5 records with name = 'John'

If you run 'update index statistics' on customer you will generate statistics for all 3 columns (id, name, age) in the
index.

When you submit the query:

select * from customer where name = 'John'

The optimizer will find the stats for the name column, determine that there are just a few records with name = 'John',
and decide that a complete index scan (5K pages) plus a handful of data page accesses (5 in this case) is cheaper than
performing a complete table scan (20K pages). In other words, 5,005 pages can be scanned faster than 20,000 pages.

Again, there are (obvious ?) caveats ... poor/missing/out-of-date stats and a large volume of records with name = 'John'
could see the optimizer still choosing an index scan, but with really lousy performance as it performs 1000's of data
page accesses (as opposed to the handful it *thinks* it will have to do) ... heavy index fragmentation may lead the
optimizer to determine that a full table scan using 16KB IOs is faster than an index scan using 2KB IOs ... etc ... ymmv

------------

If these scenarios don't describe your situation and you want to efficiently access your data based on columns other
than (id), (id, name) or (id, name, age) then yes, you'll want to add a new index.

For you sample query it would certainly be a good idea to have a new index that has 'name' as the leading column.

Whether the index has additional columns will depend on the types of queries you plan on using to access the customer
table and the SARG/join requirements of said queries.

RGS wrote:
> Hi!
> I am working with Sybase ASE 15.0.3
>
> I have a table: customer
> Table has a noncluster index by fields: (id,name,age)
>
> If I execute:
>
> select * from customer where name = 'John'
>
> ASE use the index? I know that the SARGS work well only if
> I use the first field in the index, for example:
>
> select * from customer where id = '1332' and name = 'John'
>
> I don't have to create a second index by the unique feld:
> "name"?
>
> Thanks