Thanks for that Rob, guess thats another whole new world I need to learn!
"Rob Cowell" <rjc4687_at_hotmail.com> wrote in message
news:3F951985.479EE4A2_at_hotmail.com...
>
>
> Harry wrote:
> >
> > I've been asked to provide metrics for an application I'm developing. I
need
> > to work out how much disk space is required for each record in the
following
> > table so I can * by no of expected records so they can create the
> > tablespace -
> >
> > CREATE TABLE HW_RESULTS
> > (
> > RESULT_ID NUMBER(8) NOT NULL,
> > RESULT_YEAR NUMBER(4) NOT NULL,
> > ARISING_REGION_ID NUMBER(8) NOT NULL,
> > ARISING_SUBREGION_ID NUMBER(8) NOT NULL,
> > ARISING_SUBREGION_DESCR VARCHAR2(50) NOT NULL,
> > ARISING_DISTRICT_ID NUMBER(8) NOT NULL,
> > ARISING_DISTRICT_DESCR VARCHAR2(50) NOT NULL,
> > CONSIGNEE_REGION_ID NUMBER(8) NOT NULL,
> > CONSIGNEE_SUBREGION_ID NUMBER(8) NOT NULL,
> > CONSIGNEE_SUBREGION_DESCR VARCHAR2(50) NOT NULL,
> > CONSIGNEE_DISTRICT_ID NUMBER(8) NOT NULL,
> > CONSIGNEE_DISTRICT_DESCR VARCHAR2(50) NOT NULL,
> > WASTE_CLASS VARCHAR2(6) NOT NULL,
> > WASTE_FATE_ID NUMBER(8) NOT NULL,
> > QUANTITY NUMBER(12,5) NOT NULL
> > )
> >
> > Is there a simple way to do this to give me say 60 bytes?
> >
> > thanks
> >
> > harry
>
> IIRC
>
> Numbers will take CEIL(precision/2)+1 bytes
>
> Varchars will take precision bytes
>
> Dates take 7 bytes
>
> Add 1 byte per column
>
> All this assumes your columns are fully populated. If you only have 10
> characters in a varchar2(50) you only need 10 bytes to store them.
>
>
> If you already have a table with some data taking the AVG(VSIZE(column))
> will let you know how much space the average column on a row is taking.
> Add all your columns together and you have an average row length.
>
> Or compute statistics and look at AVG_ROW_LEN in USER_TABLES.
>
> I *think* VSIZE includes the 1 byte per column overhead, but
> AVG_ROW_LENGTH doesn't.
>
> Then of course you can factor in block and segment overheads. Or you can
> think life is too short and just add 10% to your original guess and
> think that's close enough.
Received on Tue Oct 21 2003 - 08:18:12 CDT
