The number(15,0) will take at most 9 bytes
The number(13,0) will take at most 8 bytes each
So your estimate should be 496 - 13 - 14 - 14 = 455
Then you need to add one byte per column to get 471.
Then you have to allow for block size, which means 8,066 bytes available from an 8KB block size with pctfree 0, initrans 2 (default) and ASSM
Max rows = trunc(8066 / 471) = 17 rows per block,
At 100M rows that's 5,882,353 data blocks.
If you create the table using a large extent size *8MB min) you get 1 bitmap block for every 128 blocks allocated so your block requirement goes up by 128/127,
so a total of 5,928,671 blocks. Round that up to the nearest 64MB (assumed extent size) - 5,931,008 blocks = 45.25GB.
So even with several errors on the way you got pretty close to the "right" answer.
Realistically, though, you're unlikely to fill all those 40 and 50 character columns, and unless you're very carefull with setting pctfree (and maybe playing around with the Hakan factor) you're probably going to run into problems with getting too many rows into a block on the initial insert and running into problems with row migration.
There's also the question of multi-byte character sets - are you thinking of your varchar2(N) declarations N bytes (the default assumption) or N characters (which, depending on character set could mean up to 4N bytes).
Regards
Jonathan Lewis
On Sun, 21 Feb 2021 at 17:03, Pap <oracle.developer35_at_gmail.com> wrote:
