Re: Size estimation

Or

Simply create the table and indexes in a test environment and add 100K dummy records, record the size and multiply to scale. no need for maths and also fills indexes so you will know the sizes for those as well :-)

Jack van Zanen

> What JL wrote, and you did only ask about the size for the table.
>
>
>
> BUT, since you marked a primary key that is almost certainly supported by
> an index and you may have additional indexes, so you’ll need to tack space
> for indexes on to get total storage requirements.
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Jonathan Lewis
> *Sent:* Sunday, February 21, 2021 1:29 PM
> *To:* Oracle L
> *Subject:* Re: Size estimation
>
>
>
>
>
> 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:
>
>
>
> Hi Listers, It's Oracle RDBMS version 11.2.0.4 exadata. We have a table
> with structure as below which is going to be created as part of a new
> project. And we want to predict the storage/space requirement for this. It
> may not be the exact size but at least we want to estimate the AVG and
> MAXIMUM space requirement for the table , if all the columns filled with
> not null values with max column length being occupied/filled for each of
> the columns.
>
>
>
> So to estimate the maximum space requirement , is it correct to Just add
> the length of the column as it is in bytes and multiply it with the
> projected number of rows. Something as below.
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l