Size estimation

From: Pap <oracle.developer35_at_gmail.com>
Date: Sun, 21 Feb 2021 22:33:02 +0530
Message-ID: <CAEjw_fgNUqtyD6x7FeTgy8C6tGGLuuvEzx8yiwNwSoNf4-Etpg_at_mail.gmail.com>

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.

Here max size for Number datatype will be 22 bytes, for Timestamp 11 bytes, for Date data type is 7 bytes. And considering other varchar data type max length is as per defined length, can i consider the row length max for these below 21 columns table will be
(22+7+50+50+40+40+30+30+3+5+2+50+11+50+22+5+1+5+22+1+50)=496 bytes and as per that the max space consumption for 100million row will be 100M*496=496million bytes=~.46GB . Is this the correct calculation? And if this is correct max space usage calculation, how can I reach out to the Avg space usage of the table?

Column Name Data type Nullable Primary Key C1 Number(15,0) Not null Yes
C2 Date Not null Yes

C3 Varchar2(50) Null
C4 Varchar2(50) Null
C5 Varchar2(40) Null
C6 Varchar2(40) Null
C7 Varchar2(30) Null
C8 Varchar2(30) Null
C9 Varchar2(3) Null
C10 Varchar2(5) Null

C11 Varchar2(2) Null
C12 Varchar2(50) Null
C13 timestamp Null
C14 Varchar2(50) Null
C15 Number(13,0) Null

C16 Varchar2(5) Null
C17 Varchar2(1) Not null
C18 Varchar2(5) Null

C19 Number(13,0) Not null
C20 Varchar2(1) Null
C21 Varchar2(50) Null

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

Received on Sun Feb 21 2021 - 18:03:02 CET