Size and number limitations

Table 6-1 lists the limitations on size and number of objects in a Sybase IQ database. In most cases, computer memory and disk drive are more limiting factors.

Table 6-1: Sybase IQ database object size and number limitations

Item

Limitation

Catalog file size

Maximum is 1TB for all platforms except for Windows systems with FAT 32-file systems, which have a 4GB limit. Windows systems with NTFS support the 1TB maximum. Sybase IQ does not support creating dbspaces on NAS (Network Attached Storage) devices.

Database name size

250 bytes.

Database size

Maximum database size approximates the number of files times the file size on a particular platform, depending on the maximum disk configuration.

Refer to your operating system documentation for kernel parameters that affect the maximum number of files.

Dbspace size

Raw: No limit – as large as the device allows.

Operating system files: 4TB.

Field size

255 bytes for BINARY, 32,767 bytes for VARBINARY

32,767 for CHAR, VARCHAR.

Up to 512 TB for 128 KB pages or 1 PB for 512 KB pages for LONG BINARY, LONG VARCHAR.

IQ page size

Must be between 64KB and 512KB.

Maximum key size

255 bytes for single-column index. 5300 bytes for multicolumn index.

Maximum length of SQL statement

The maximum length of SQL statements is limited to the amount of memory available for the IQ catalog, and to the size of the catalog stack.

If your SQL statements are long, increase the catalog stack size using -gss, and increase catalog memory cache size using -c or a combination of -ch and -cl.

When printing the SQL statement in error messages, the text is limited to the IQ catalog page size. To print long commands, you can start the server with an increased -gp setting, although in general Sybase recommends that you use the default of -gp 4096.

Maximum length of variable-length FILLER column

512 bytes.

Maximum number of users (connected and concurrent)

1000 on 64-bit platforms AIX, HP, Linux, and Sun Solaris.

200 on 32- and 64-bit platforms on Windows.

Maximum size of temp extract file

Set by TEMP_EXTRACT_SIZEn option. Platform limits are:

AIX & HP-UX: 0 – 64GB

Sun Solaris: 0 – 512GB

Windows: 0 – 128GB

Linux: 0 – 512GB

Number of columns per table

Sybase IQ supports up to 45,000 columns in a table. You may see performance degradation if you have more than 10,000 columns in a table.

Number of events per database

2^31 – 1 = 2 147 483 647.

Number of files per database

Operating system limit that user can adjust; for example, using NOFILE. Typically, 2047 files per database.

Number of indexes

2^32 (~4,000,000) per table.

Number of rows per table

Limited by table size, upper limit 2^48.

Number of stored procedures per database

2^32 – 1 = 4 294 967 295.

Number of tables or views in a single FROM clause

16 – 64, depending on the query, with join optimizer turned on.

Number of tables or views referenced per query

512.

Number of tables per database

4,293,918,719.

Number of tables per join index (number of tables that can be joined in one query block)

32.

Number of tables referenced per transaction

No limit.

Number of UNION branches per query

512. If each branch has multiple tables in the FROM clause, the limit on tables per query reduces the number of UNION branches allowed.

Number of values in an IN list

250,000.

Row size

Sybase recommends a limit of half the page size.

Table size

Limited by database size.