Re: RowSize Equation in Oracle ServerAdmin Guide for 7.3.4 and 8.0.#

Answer mixed in with extract from manual.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Jorge Meirim wrote in message <7fq9oh$sqc$2_at_pthp35.telecom.pt>...
>From the manual:
>Step 3: Calculate the
>Space Used per Row
>=
>column size + (1, if column size < 250, else 3)

If column length is < 250 then add 1 for the 'length of column' variable, otherwise add 3.

>Note: You can also determine column size empirically, by selecting
>avg(vsize(colname)) for each column in the table.
>Then, calculate the row size:
>Rowsize
>=
>row header (3 * UB1) + sum of column sizes including length bytes

The size of the row header is 3 x the length of type UB1, where UB1 is define in v$typesize (? v$type_size)- different platforms use different numbers of bytes to store different Oracle structures, a 'UB1' should be an 'unsigned single byte' but may actually be larger than one byte.

>Finally, you can calculate the space used per row:
>Space used per row (rowspace)
>=
>MIN(UB1 * 3 + UB4 + SB2, rowsize) + SB2
>Where:
>

A row may actually need to become a row header plus a pointer to the migrated row, so Oracle reserves enough space in each block for this option irrespective of how small your row is. This minimum is:

    3 'Unsigned single byte structures'
    1 'Unsigned 4 byte structures'
    1 'Signed 2 byte structure'

And there is always a 'signed 2 byte structure' in the row index at the top/bottom of the block.

PS - the biggest discrepancy in sizing comes on things like PCs, I think, where 'odd byte' structures are padded up to an even number of bytes. Received on Fri Apr 23 1999 - 12:59:40 CDT