> >Fact? :
> >max no of records per table = 2^31 - 1
>
> Fiction. The number of records in a table is a
> complex function of page and record size.

Some time ago I was trying to find out more accurate answer,
but I had to stop my research due to lack of time.
The problem is as follows (correct me where I am wrong):
- each row in table is located by db_key, which is 64-bit value,
- db_key consists of 32-bits representing table-id, and
32-bits representing "record number".
- so "potential" address space for rows in table is 2^32,
but this theoretical maximum can never be reached, because
- 32-bit record number is further structured, and consists of
-pointer page number
-offset on that pointer page
-offset into pointer to row on data page
- I am not familiar enough with IB source code yet,
so I could not find algorithm for "(un)packing" these three parts
(from)into single 32-bit record number value, but I am sure it is not
as simple as e.g. [10-bits pointer page, 10-bits offset, etc...]

"offset into pointer to row on data page" has its maximum
for given page length, because only limited number of rows can fit
on data page (e.g. max. 35 records for 1024 page_size).
"offset on pointer page" has also its maximum for given page length
(about 230 for 1024 page_size)
"pointer page numbers" are chained and are given consecutive values
(that can be found in rdb$pages table).

- now after some theory let's make little experiment:
-create database
-disable reserving space for inserted rows (by gfix -use full)
-create table with short rows, e.g. create table tab(x char(1));
-fill that table, e.g. by stored procedure with loop
-look at db_keys: select rdb$db_key from tab;

-you will find out that even if you use shortest rows possible
there are quite large gaps in db_key numbers.
e.g. for page size 1024 max. 35 rows will fit on one data page,
so you can see db_keys
1,2,3, ... 22,23 ( 23(16)=35(10) )
and next "batch" of rows is numbered
3B,3C, ... 5C,5D ( 3B(16)=59(10) )

as you can see, db_keys 24..3A (i.e. 23 values) are lost!
(other page sizes are not much better, you will see similar
"wasting" of record number address space).
(Surprisingly, after inserting some amount of rows,
the gaps gets even larger!)

So, am I really completely wrong, or is the algorithm for assigning
record numbers not_as_good_as_it_could_be ?


In one older message, Jim Starkey wrote:
=====
So to compute a reliable record number from (point page#, data page#,
line#), you need to know the maximum records per data page, and that's
a problem. There is no nice trade-off here. You can decide to use
the maximum number of minimum size records (theoretical minimum),
which wastes record number space (every data page with few records
leaves holes in the number space) or pick an arbitrary number out of
hat, which leaves the potential for wasting data page space because
a page ran out of line numbers.
=====
It seems to me that IB uses even higher value than maximum in
"maximum number of minimum size records (theoretical minimum)"


Ivan
prenosil@...
http://www.volny.cz/iprenosil/interbase