Index size

Table 5-1 describes the limits for index size for APL and DOL tables:

Table 5-1: Index row-size limit

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.