Re: How to get table record length in bytes?

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 - 06:33:25 CDT