Re: Size estimation

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
> <mailto: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>
> [mailto: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
> <mailto: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