Hello,
I was wondering if someone knows a better way to explain how different indexes
work. Such as, is it better to have an index with int type or char or varchar
etc? Is it better to have composite indexes or indexes with individual columns?
How does optimizer handle different indexes and what can we do help it? Indexes
are very efficient most of the time but they can be quite detrimental to
server's performance, if abused. Perhaps you know of a good article about
indexing that you could share with us. Or, perhaps Mr. Rob Verschoor would like
to issue an article in his very useful website. I have been asked these
questions quite alot, and it is time I get some definitive answers from other
experts out there.

Knowledge is power, please do share.

Thanks............Amer
---== Posted via the PFCGuide Web Newsreader ==---
http://www.pfcguide.com/_newsgroups/group_list.asp

"Amer Khan" <ak500@dcx.com> wrote in message
news:BBKyLs$$BHA.132@forums.sybase.com...
> Hello,
> I was wondering if someone knows a better way to explain how
different indexes
> work. Such as, is it better to have an index with int type or char
or varchar
> etc? Is it better to have composite indexes or indexes with
individual columns?
> How does optimizer handle different indexes and what can we do help
it? Indexes
> are very efficient most of the time but they can be quite
detrimental to
> server's performance, if abused. Perhaps you know of a good article
about
> indexing that you could share with us. Or, perhaps Mr. Rob Verschoor
would like
> to issue an article in his very useful website. I have been asked
these
> questions quite alot, and it is time I get some definitive answers
from other
> experts out there.
>
> Knowledge is power, please do share.
>
> Thanks............Amer



You're too kind! (and I'm short of time...) I think the ASE
Performance & Tuning Guide is a good starting point. I'd love to write
something about these things, but I'm not planning to do that anytime
soon (too many other things to do...). I'm working on a book on ASE,
and I might write about it there..

HTH,

Rob
----------------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.0/11.5/11.0

Author of "The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/qr

email mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
WWW  http://www.sypron.nl
snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
----------------------------------------------------------------------

Here's my take on these from somebody who's administered Sybase off 
and on (mostly on) for 10 years -- but never got certified or even took a 
class. 
(So keep a grain of salt handy).

> I was wondering if someone knows a better way to explain how different 
indexes
> work. Such as, is it better to have an index with int type or char or 
varchar etc? 

I've always been under the impression that numeric is better but
I think most important is the smaller the key columns the better.
So certainly a 4-byte integer would be far better than, say, a
char(8) field.  The smaller the columns are the more index 
records you can fit into a data page.

I think varchar is equivalent to char when it comes to indexes
but I could be mistaken.  In the "old days" we avoided using
varchar completely to avoid the performance hit.

> Is it better to have composite indexes or indexes with individual 
columns?

Again, for the same reason, the smaller & fewer columns
in an index the better.  If additional columns in a composite
index aren't needed, don't add them.  And if the columns takes
unselective values (like a type field that only takes a few
values), don't add them (unless they're needed in an important
covering query).

> How does optimizer handle different indexes and what can we do help it? 

Best thing you can do to help it is to keep index statistics updated.
You may also need to tweak the stats with optdiag directly.
For example, if you have a key-column that is increasing the
max value will soon be out of range from when the indexes
were taken.  You can also help by not willy nilly adding indexes.
Additional indexes mean additional overhead and gives the
optimizer additional choices that it may not need.

Perhaps others can elaborate...

Thanks, That was great info. I appreciate that greatly.

On Tue, 21 May 2002 08:55:31 -0400,
 in sybase.public.ase.general
 <mc> wrote: 
>Here's my take on these from somebody who's administered Sybase off 
>and on (mostly on) for 10 years -- but never got certified or even took a 
>class. 
>(So keep a grain of salt handy).
>
>> I was wondering if someone knows a better way to explain how different 
>indexes
>> work. Such as, is it better to have an index with int type or char or 
>varchar etc? 
>
>I've always been under the impression that numeric is better but
>I think most important is the smaller the key columns the better.
>So certainly a 4-byte integer would be far better than, say, a
>char(8) field.  The smaller the columns are the more index 
>records you can fit into a data page.
>
>I think varchar is equivalent to char when it comes to indexes
>but I could be mistaken.  In the "old days" we avoided using
>varchar completely to avoid the performance hit.
>
>> Is it better to have composite indexes or indexes with individual 
>columns?
>
>Again, for the same reason, the smaller & fewer columns
>in an index the better.  If additional columns in a composite
>index aren't needed, don't add them.  And if the columns takes
>unselective values (like a type field that only takes a few
>values), don't add them (unless they're needed in an important
>covering query).
>
>> How does optimizer handle different indexes and what can we do help it? 
>
>Best thing you can do to help it is to keep index statistics updated.
>You may also need to tweak the stats with optdiag directly.
>For example, if you have a key-column that is increasing the
>max value will soon be out of range from when the indexes
>were taken.  You can also help by not willy nilly adding indexes.
>Additional indexes mean additional overhead and gives the
>optimizer additional choices that it may not need.
>
>Perhaps others can elaborate...
>

---== Posted via the PFCGuide Web Newsreader ==---
http://www.pfcguide.com/_newsgroups/group_list.asp