RE: Rows limit, SE 5.01

---------- From: Art S. Kagel[SMTP:kagel@bloomberg.com] Sent: Thursday, February 12, 1998 11:49 AM Art Kagel wrote: >Billy Wheeler wrote: >> On 11 Feb 98 at 17:06, Nick Nobbe wrote: >> > Is there such a thing as too many rows in SE. [...] >> .> There is a limit. Unfortunately, I've lost the e-mail where Jonathan >> gave me the numbers... :-( >[...] >For SE the limit is 2^31 rows but is limited by the maximum table size >of 2^31 bytes... This much I agree with, give or take one byte. This is 2GB - 1 byte. >... so the practical limit is 1,048,576 pages of data @ 2020 >bytes per page less any slack on each page. Slack is 2020%(rowsize+8). >So number of rows per page is 2020 / (rowsize + 8) and max rows for you >table is: 1048576 pages * rows_per_page. This is based on OnLine calculations which simply do not apply to SE. There is a 1-byte overhead on the length of each row in the C-ISAM data file, so the maximum number of rows in a table satisfies the inequality: Number-of-Rows * (Row-Length + 1) <= 2**31 - 1 There is an additional constraint that the total size of all the indexes must also be less than 2GB. And, of course, you must have enough disk space for these files, but that isn't the problem it used to be when 300 MB drives were the big drives. The overhead byte always contains either the value '\\0' or '\\n'. The NUL means the row was logically deleted; the newline means that the row is current. At a more practical level, access to SE starts creaking when the size of the table is such that the index gets too deep (4 or 5 levels in the B+-tree?), or the size gets big enough - which I used to reckon as somewhere around 50-100 thousand rows at, say, 256 bytes per row. Whether this reckoning is still accurate is open to discussion (as is whether it was ever accurate). The estimate is about 5 years old and computers have changed since then. I'm assuming that I might, conceivably, be the Jonathan to whom Billy refers. I don't have a record of the message I sent to Billy, either. Yours, Jonathan Leffler (jleffler@visa.com) #include <memory.h>