Re: Rows in a table

nolan_at_helios.unl.edu (Michael Nolan) writes:
|> 170sys_at_netcom.com (170 Systems) writes:
|>
|> :Lynn Wood (lynnwood_at_crash.cts.com) (aka Greg) wrote:
 

|> :: My questions to any employees at Oracle & Informix is:
 

|> :: 1. What is the maximum no of rows that can be stored.
|> :: (Above figures only account for current volume, and not for growth)
|> :Oracle: no maximum
|>
|> I'm not sure this is entirely accurate, though from a practical point of view
|> there is no limit on the number of rows in Oracle that one could reasonably
|> expect to hit.
|>
|> The ROWID appears to be a 16 digit hexadecimal field, which means that
|> there would be a limit of 256^16 rows assuming that a ROWID +must+ be unique.
|> Thus, the limit is 340,282,366,920,938,463,374,607,431,768,211,456 rows. :-)
|>
|> A somewhat more practical limit is the maximum size of an Oracle database,
|> which (depending on which answer you believe) is somewhere between 4 and 35
|> terabytes. You get this by multiplying the maximum number of blocks
|> by the maximum block size. According to the Version 7 SAG, the maximum
|> number of blocks is 2^32 -1. The maximum block size (under UNIX, at least)
|> is 8K. Multiply these together and you get 35 terabytes.
|>
|> However, the SAG also says that the maximum is 4 terabytes, depending on
|> block size, so I'm not sure which answer is correct. (The minimum block
|> size is 1K, which may be where the 4TB number comes from.) In either case,
|> it's a whole lot more data than I'm likely to ever have.

On Unix systems, file size is limited to the largest value that can be held in the datatype returned by the lseek() system call. Many still have a maximum of 2 gigabytes. On the Oracle7 side, the maximum number of datafiles allowed in a database is 1022 on most platforms. This means that the largest database allowed is 2T in this example. However, you need to subtract from that the file needed for the system tablespace (if you want to keep it separate), then take into account space needed for rollback segments, index(es) on that large table, and the temporary space needed to create those indexes (and to perform many types of queries). I haven't gotten to the overhead needed by Oracle blocks. Again, Michael is right, this still leaves much more space than is needed by most people.

For the few where it isn't enough room, there's always the option of splitting the table among multiple databases and taking advantage of the distributed features of Oracle to try to make them seem like one very humongous logical table.

Always a fun thing to think about.

Roderick Manalac
Oracle Corporation

DISCLAIMER: The views expressed here do not necessarily reflect those of

             this station or its advertisers. Received on Mon Feb 20 1995 - 00:05:44 CET