| Subject | Re: [firebird-support] Maximum number of rows in a table |
|---|---|
| Author | Ann W. Harrison |
| Post date | 2005-03-11T20:20:36Z |
Aitor Gómez González wrote:
in InterBase, a record number is a 32=bit quantity, but the assignment
of record numbers is not dense. The record number is divided into three
parts.
The first is the sequence number of a pointer page. Each table has a
list of pointer pages which contain the page numbers of data pages.
The second part is the offset on the pointer page that contains the
number of the data page.
The third part is the offset on the data page that contains the pointer
to the actual record and its length. That location is called a page index.
The number is divided up differently, depending on the page size.
First the system determines the maximum number of records that could
possibly fit on a page. It subtracts the page overhead from the page
size and divides the result by the size of a record header plus the page
index. The result of the division is the maximum number of records that
could fit on a page.
Next, the system determines the number of data page pointer that can fit
on a pointer page by subtracting the pointer page overhead from the page
size and dividing the result of the subtraction by the size of a page
pointer.
Finally, the system divides 4Gb by (the number of records per data page
* the number of data pages per pointer page). The result is the maximum
number of pointer pages that can be referenced.
So, if you actually stored completely empty records, you could in fact
store 4,294,967,295 records. Unfortunately, few people actually store
empty records. If the average record size is 10 bytes, you can store
2,704,238,667 records. If the average record size is 50 bytes, you can
store only 1,089,767,821 records.
Version 2 increases the record number to 40 bits from 32.
Regards,
Ann
>That's right, and it's fixed in Firebird 2.0. In earlier versions and
> I thought (probably wrongly) that maximum number of rows in a table
> was limited by the BIGINT range until I found this morning the
> following information at the IBSurgeon site:
>
> Actual maximum number of rows
> http://www.ib-aid.com/interbase/firebird/bug/research.html
>
> Is this bug present in Firebird? How many rows can be stored in a table?
>
in InterBase, a record number is a 32=bit quantity, but the assignment
of record numbers is not dense. The record number is divided into three
parts.
The first is the sequence number of a pointer page. Each table has a
list of pointer pages which contain the page numbers of data pages.
The second part is the offset on the pointer page that contains the
number of the data page.
The third part is the offset on the data page that contains the pointer
to the actual record and its length. That location is called a page index.
The number is divided up differently, depending on the page size.
First the system determines the maximum number of records that could
possibly fit on a page. It subtracts the page overhead from the page
size and divides the result by the size of a record header plus the page
index. The result of the division is the maximum number of records that
could fit on a page.
Next, the system determines the number of data page pointer that can fit
on a pointer page by subtracting the pointer page overhead from the page
size and dividing the result of the subtraction by the size of a page
pointer.
Finally, the system divides 4Gb by (the number of records per data page
* the number of data pages per pointer page). The result is the maximum
number of pointer pages that can be referenced.
So, if you actually stored completely empty records, you could in fact
store 4,294,967,295 records. Unfortunately, few people actually store
empty records. If the average record size is 10 bytes, you can store
2,704,238,667 records. If the average record size is 50 bytes, you can
store only 1,089,767,821 records.
Version 2 increases the record number to 40 bits from 32.
Regards,
Ann