Database Table Sizing

[littlechicken]

Hi all,

I am not very knowledgable in this area so would appreciate any advice you guys can give.

I need to provide a projected estimate of Oracle db tables. Essentially my problem is in trying to work out the size of one single row. I have a table which uses the varchar2, integer and date datatypes but I cannot seem to find anywhere how many storage bytes each of these use (with the exception of varchar).

As far as I understand for integers and numbers it depends on the actual number entered - is this correct? If so - is there anyway I can estimate this based on the data I know will be entered.

Also, I read somewhere that date takes 7 bytes of storage - is this correct?

All help much appreicated,
Thanks

[davey23uk]

date = 8 bytes (I think, might be 7) as for varchar and number, it depends on the actual string (not the size of the column) so take a typical row and count the number of charcters, add in the value for aa date column and you are there.

Then you need to work out indexes which is tricky

[tamilselvan]

Date is 7 bytes length.

SQL> create table t1 (rdate date);

Table created.

SQL> insert into t1 values (sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select dump(rdate) from t1 ;

DUMP(RDATE)
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,106,2,23,15,60,57

Tamil

[dbasan]

I need to provide a projected estimate of Oracle db tables. Essentially my problem is in trying to work out the size of one single row. I have a table which uses the varchar2, integer and date datatypes but I cannot seem to find anywhere how many storage bytes each of these use (with the exception of varchar).

Since you want projected estimate, you have to calculate the size of the data types for the table columns, like
C1 varchar2(21), --size 21 (variable)
C2 number, --size 38 (variable)
C3 date --size 7 (fixed)
A row will occupy c1+c2+c3 size (projected size) but actuall size is what the c1 and c2 is going to occupy depend on the data + the c3.

[mike9]

1) create your table
2) insert 10000 rows in your table with real data (e.g. a name has in most cases less then 50 characters even if the name field is defined as varchar2(50))
3) calculate the needed space per row