Solved

600 bytes composite index limit

Posted on 2004-04-04
8
1,128 Views
Last Modified: 2012-05-04
I'm getting this error when I try to create index. I know reducing the column size or number of columns would workaround the problem. but I want to know is there anyway we can increase the limit, is this something to do with page size?

we are using 12.5 server and using 2k page size.

Thanks!
0
Comment
Question by:kennethxu
  • 4
  • 2
  • 2
8 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 10753313
Size of the composite index is either 31 columns or 600 bytes. I do not think this limitation has anything to do with pagesize.

Perhaps, Bret from Sybase can confirm that.

Regards-
0
 
LVL 14

Author Comment

by:kennethxu
ID: 10753663
can anybody please, if you have a sybase server with *more* then 2k page size, try to create a table with column a varchar(350) and b varchar(350), then create an index on (a,b). and let me know the result.
thanks!
0
 
LVL 13

Expert Comment

by:alpmoon
ID: 10763110
I agree with Namasi, I don't think that it is related to page size. Actually an index with 700 bytes key length would be a really an expensive one especially for updates. I think you should avoid to create it anyway. If table is very small table scan would be OK, otherwise just use the first column.
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 10765320
Yes. Agree with alpmoon that it would be expensive for updates. May be you can consider adding an identity field to the table and make that the primary key and add non-clustered indexes on each varchar(350) fields.

regards-
0
 
LVL 14

Author Comment

by:kennethxu
ID: 10765932
I agree too, the only thing is the table schema was given by bea weblogic server. I don't know what bea is doing on this. This looks like obvious mistake, but they yet made it.
0
 
LVL 14

Author Comment

by:kennethxu
ID: 10766096
I'll leave it open for a day to two to see if anybody has a 12.5 server to try it out and confirm. then I'll award points.
0
 
LVL 13

Accepted Solution

by:
alpmoon earned 500 total points
ID: 10819304
Hi,

I came accross information about maximum index row size in Reference Manual:

Logical page size    Index column size limit
2K                           600
4K                          1250
8K                          2600
16K                        5300

So, you can create your index with 4K or 8K page size according to Reference Manual.
0
 
LVL 14

Author Comment

by:kennethxu
ID: 10819631
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sybase - sp_cacheconfig 1 1,009
SIMPLE QUESTION BACKUP AND RESTORE ORACLE DATABASE 13 91
Why did SAP buy Sybase? 3 168
I need expert Sybase Sql help with a query 3 70
In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
Riverbed Technology's webinar discusses networking for the cloud era with simplified SD-WAN cloud connectivity.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question