SMALL and LARGE tables

Use the SMALL keyword in the CREATE TABLE statement to create small, legacy-format tables in version 15.0.0.0.

Informix server version 15.0.0.0 supports significantly larger tables than prior versions, which had a limit of 16.7 million pages per fragment. The maximum page limit in version 15.0.0.0 is so much larger that these legacy tables are now referred to as small tables. Tables created in version 15.0.0.0 that may grow up to a maximum of 140 trillion pages per fragment are called large tables.

Large tables are made possible by new row addressing that uses an 8-byte identifier. Small tables use the legacy 4-byte row identifier.

A small table cannot be altered to become a large table using an ALTER TABLE statement. Nor may a large table be altered to a small table. Three supported methods of moving data from a small table to a large table are:
  • unload/load
  • selecting from the small table and inserting into the large table
  • loopback replication

Indexes on large tables will have a larger footprint than indexes on small tables, since large table indexes contain the new 8-byte rowids. The percentage difference in footprint size will depend on the size of the index key and the number of duplicate keys. In terms of page numbers, the maximum size of an index on a large table is also significantly larger than the maximum size of an index on a small table.

By default, new permanent user tables in version 15.0.0.0 are large tables. It is possible to create small, legacy-format tables in version 15.0.0.0, however, using a new SMALL keyword in the CREATE TABLE statement. You may also change the default behaviour for a session by setting the TABLE_SIZE session-level environment variable to 'SMALL'.

System catalogs and tables in system databases (sysmaster, sysadmin, sysuser, sysutils, sysha, syscdr) are always small tables.

Temporary tables are always in small format unless created using the 'large' keyword. The TABLE_SIZE environment variable does not affect temporary tables.

While an instance is in compatibility mode, large tables may not be created.

Unless they are recompiled with the latest client libraries, legacy clients will not be able to access large tables, because they are not compatible with 8-byte rowids.

If a table is created with either the "SMALL" or "LARGE" keyword, dbschema and dbexport will preserve this keyword in the generated SQL as long as the -ss option is used. The schema of a table generated by dbschema -ss or dbexport -ss will not contain the "SMALL" or "LARGE" keyword unless it was used explicitly during the table creation.