Question & Answer
Question
Maximum number of pages and rows allowed in fragmented and unfragmented tables are related to the ROWID. Encountered an sql error along with error 136, no more extents.
Answer
Limits in unfragmented tables and indexes
Every row in an unfragmented table has a number called a ROWID that uniquely identifies the row within the table and also indicates where in the table the row is stored. The ROWID for a row is represented by a 4 byte integer, the most significant 3 bytes of that integer indicate the page where the row is stored and the least significant byte indicates the slot on that page where the row is stored.
- Tip: If you represent the ROWID as a hexidecimal number the leftmost 6 digits are the page number and the rightmost 2 digits are the slot number.
- Example:
A row with a ROWID of 0x4F0033 is located in slot 0x33 (51) of page 0x4F00 (20224).
3 bytes are used to store the page number a maximum of 16,777,215 pages can be used in one table.
1 byte is used to store the slot number, a single page can have at most 255 slots/rows.
The maximum number of rows in a table or fragment is 4,278,189,825.
These are all theoretical limits. The practical limits are lower because of the following:
- Pages are also used for purposes other than rows of data. Examples include attached indexes, and bitmap pages.
- Some pages are used as remainder pages.
- To fit the maximum number of rows in a data page the rows must be very small. Most of the time fewer rows are stored on a page, reducing the maximum number of rows that the table can hold.
Limits for fragmented tables and indexes
If a table is fragmented then the ROWID for each row is only guaranteed to be unique within its fragment. So to determine the limits in a fragmented table multiply the limits for an unfragmented table times the number of fragments used by your table. (The number of rows per page remains the same, of course)
OnLine and Informix Dynamic Server (IDS) engines allow a maximum of 2047 fragments in a single table. IBM Informix Extended Parallel Server (XPS) allows 32766.
[{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"10.0;11.1;11.5;11.7;12.1","Edition":"Enterprise","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSGHZP","label":"Informix OnLine"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Not Applicable","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSGU5D","label":"Informix Extended Parallel Server"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Not Applicable","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21105506