Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Database Sizing
On 23 Feb 2006 01:01:48 -0800, "littlechicken"
<kerryjane.conlon_at_gmail.com> wrote:
>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
>
Hi,
There are basicly 2 approaches :
The theoretical :
Dig down to the Oracle internals to find out how Oracle really stores
its datatypes. You have already found out that date takes 7 bytes of
storage. You have also figured out that a number(30) column containing
23 takes up less space than that column containing 232323232323.
You thus also need to know the column distributions, i.e. if your
varchar2(100) contains on average 10 bytes it will take up less space
than it containing 70 bytes.
The practical :
Get a sample set of data from the customer. Download Oracle Express,
install it on your laptop and load data into you table. Then issue
"analyze table <table> compute statistics" and get a lot of nice
statistics like average row length etc. You can also gather other
useful statistics yourself from the data dictionary and built-in
packages.