• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1169
  • Last Modified:

600 bytes composite index limit

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
kennethxu
Asked:
kennethxu
  • 4
  • 2
  • 2
1 Solution
 
namasi_navaretnamCommented:
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
 
kennethxuAuthor Commented:
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
 
alpmoonCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
namasi_navaretnamCommented:
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
 
kennethxuAuthor Commented:
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
 
kennethxuAuthor Commented:
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
 
alpmoonCommented:
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
 
kennethxuAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now