# what is the max. number of records in a table

• 09-24-2001, 12:36 PM
Chandra
Hi,
I would like to know what is the max number of rows we
can put in partitioned table or per partition.
Hear i have to insert 1.2 billion per partition.
i would like to know your openion.

Thank
• 09-24-2001, 12:39 PM
sambavan
I wouldn't think so...

Sam
• 09-24-2001, 12:53 PM
Chandra
Here is my hardware + software configuration
Sun E 3500. 4x400MHZ CPU, 2 GB RAM. 2xA5200 Disk arrays.
Veritas VM, Veritas QuickI/O filesystem.

Thanks
• 09-24-2001, 01:46 PM
pando
unlimited
• 09-24-2001, 02:05 PM
tamilselvan
Oracle allows two bytes for row numbers, so theoretically they could go as high as 65535 rows in a block. However, given the minimum row length (11 bytes) and maximum database block size (32K), the pratical limit on the number of rows in a block is in fact only 2970.

For calculation purpose Let us say 3000 rows in a block of 32K (32768).

The size of a datafile for a Oracle tablespace is 4 GB ( 4,294,967,296 bytes).

# of rows in a 4GB datafile = (4294967296/32768)*3000 = 393,216,000 rows.

Assume a tablespace can support 1022 datafiles.

So max # of rows for a table = 393216000 * 1022 = 401,866,752,000 Rows.

So a table can have maximum of 400 billion rows if the row is size 11 bytes.

If I want to store 400 billion rows in a table, then I would not use Oracle, but Teradata.

Max # of partitions in a table = 64000

So a table can have (64000 * 400) billion rows.

[Edited by tamilselvan on 09-24-2001 at 02:32 PM]