Table 5-1 describes the limits for index size for APL and DOL tables:
Page size |
User-visible index row-size limit |
Internal index row-size limit |
---|---|---|
2K (2048 bytes) |
600 |
650 |
4K (4096 bytes) |
1250 |
1310 |
8K (8192 bytes) |
2600 |
2670 |
16K (16384 bytes) |
5300 |
5400 |
You can create tables with columns wider than the limit for the index key; however, these columns become nonindexable. For example, if you perform the following on a 2K page server, then try to create an index on c3, the command fails and Adaptive Server issues an error message because column c3 is larger than the index row-size limit (600 bytes).
create table t1 (c1 intc2 intc3 char(700))
You can still create statistics for a nonindexable column, or include it in search results. Also, if you include the column in a where clause, it is evaluated during optimization.
An index row size that is too large can result in frequent index page splits. Page splits can make the index level grow linearly with the number of rows in the table, making the index useless because the index traverse becomes expensive. Adaptive Server limits the index size to, at most, approximately one third of server’s page size, so that each index page contains at least three index rows.