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.

Enen in ASE 15; why maximum size of index is limited to 600 bytes (at least for a 2K page server

6 posts in General Discussion Last posting was on 2012-08-21 20:34:10.0Z
tartampion Posted on 2012-08-14 18:37:46.0Z
Sender: 7c2d.502a99a7.1804289383@sybase.com
From: tartampion
Newsgroups: sybase.public.ase.general
Subject: Enen in ASE 15; why maximum size of index is limited to 600 bytes (at least for a 2K page server
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <502a9afa.7c55.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 14 Aug 2012 11:37:46 -0700
X-Trace: forums-1-dub 1344969466 172.20.134.41 (14 Aug 2012 11:37:46 -0700)
X-Original-Trace: 14 Aug 2012 11:37:46 -0700, 172.20.134.41
Lines: 33
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31343
Article PK: 74231

How the 600 bytes is maximum size is calculated? if the
total size of index is more than 600, the error 1903 is
produced, doe sit mean that teh size of index can not be a
bit mor ethan half of page size?
Severity
16
Message text
%d is the maximum allowable size of an index. Composite
index specified is %d bytes.
Explanation
A composite index is any index that uses from 2 to 16
columns. Error 1903 occurs when the sum of the lengths of
all the columns used in a composite index exceeds the
allowable limits.

Following is an example of a composite index using objects
from the pubs2 sample database:

1> create index example
2> on authors (phone, state, postalcode)
3> go
phone is defined as char(12), state as char(2), and
postalcode as char(10). Since each char takes 1 byte of
storage, the total length of this composite index is 24
bytes.

Action
Examine the lengths of the columns in your composite index
and make sure that the lengths total no more than 600 bytes.
Refer to “System Datatypes” and “User-Defined
Datatypes” in the Adaptive Server Enterprise Transact-SQL
User's Guide for information about how to calculate the
storage size for different Adaptive Server datatypes.


Bret Halford Posted on 2012-08-15 15:41:58.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:14.0) Gecko/20120713 Thunderbird/14.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Enen in ASE 15; why maximum size of index is limited to 600 bytes (at least for a 2K page server
References: <502a9afa.7c55.1681692777@sybase.com>
In-Reply-To: <502a9afa.7c55.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: <502bc346$1@forums-1-dub>
Date: 15 Aug 2012 08:41:58 -0700
X-Trace: forums-1-dub 1345045318 172.20.134.152 (15 Aug 2012 08:41:58 -0700)
X-Original-Trace: 15 Aug 2012 08:41:58 -0700, vip152.sybase.com
Lines: 21
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31344
Article PK: 74235


On 8/14/2012 12:37 PM, tartampion wrote:
> How the 600 bytes is maximum size is calculated? if the
> total size of index is more than 600, the error 1903 is
> produced, doe sit mean that teh size of index can not be a
> bit mor ethan half of page size?

The size of the index is calculated by summing the (maximum)
size of the keys.

The 600 byte maximum is itself somewhat arbitrary; it is
small enough so that there can be at least 3 index
entries on each index page on a 2k page size server.
This leads to quite tall indexes as is, a wider limit
that resulted in only two entries per page would be
far worse.

-bret
.


tartampion Posted on 2012-08-15 16:55:50.0Z
Sender: 340e.502ba35f.1804289383@sybase.com
From: tartampion
Newsgroups: sybase.public.ase.general
Subject: Re: Even in ASE 15; why maximum size of index is limited to 600 bytes (at least for a 2K page server
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <502bd496.3d9a.1681692777@sybase.com>
References: <502bc346$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 15 Aug 2012 09:55:50 -0700
X-Trace: forums-1-dub 1345049750 172.20.134.41 (15 Aug 2012 09:55:50 -0700)
X-Original-Trace: 15 Aug 2012 09:55:50 -0700, 172.20.134.41
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31345
Article PK: 74236

Thanks Bret.

> On 8/14/2012 12:37 PM, tartampion wrote:
> > How the 600 bytes is maximum size is calculated? if the
> > total size of index is more than 600, the error 1903 is
> > produced, doe sit mean that teh size of index can not be
> > a bit mor ethan half of page size?
>
> The size of the index is calculated by summing the
> (maximum) size of the keys.
>
> The 600 byte maximum is itself somewhat arbitrary; it is
> small enough so that there can be at least 3 index
> entries on each index page on a 2k page size server.
> This leads to quite tall indexes as is, a wider limit
> that resulted in only two entries per page would be
> far worse.
>
> -bret
> .
>


John McVicker Posted on 2012-08-21 12:53:04.0Z
Sender: 4af5.50338347.1804289383@sybase.com
From: John McVicker
Newsgroups: sybase.public.ase.general
Subject: Re: Enen in ASE 15; why maximum size of index is limited to 600 bytes (at least for a 2K page server
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <503384b0.4b59.1681692777@sybase.com>
References: <502a9afa.7c55.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 21 Aug 2012 05:53:04 -0700
X-Trace: forums-1-dub 1345553584 172.20.134.41 (21 Aug 2012 05:53:04 -0700)
X-Original-Trace: 21 Aug 2012 05:53:04 -0700, 172.20.134.41
Lines: 12
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31349
Article PK: 74240

An index of 600 bytes wide is a little suspect in the design
aspects of that index. Maybe a lot suspect. What would
cause an index to be that wide? Do you have dozens of
columns or really-wide included columns like char(255) * 3?

If an attempt is being made to build a covering index using
many columns, how much better would that be than doing a
good job designing a narrower index? Have benchmarking and
monitoring metrics proven the design to be far superior to a
narrower index?

- John