Re: Size estimation

Yes this way through explain plan also the size coming as ~4MB, same as I have manually created for 100K rows.

Regards
Pap

On Mon, Feb 22, 2021 at 1:38 PM Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> if you do an explain plan on an "create index " statement you get a size
> estimate (Uncompressed).
>
> Regards
>
> Lothar
>
> Am 22.02.2021 um 01:48 schrieb Jack van Zanen:
>
> 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
>
>
> -------------------------
> This e-mail and any attachments may contain confidential material for the
> sole use of the intended recipient. If you are not the intended recipient,
> please be aware that any disclosure, copying, distribution or use of this
> e-mail or any attachment is prohibited. If you have received this e-mail in
> error, please contact the sender and delete all copies.
> Thank you for your cooperation
>
>
> On Mon, Feb 22, 2021 at 9:35 AM Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
>> 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