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
![]() |
0 |
![]() |
"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 ----------------------------------------------------------------------
![]() |
0 |
![]() |
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...
![]() |
0 |
![]() |
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
![]() |
0 |
![]() |