Re: Table Fragmentation

I agree,

but I think you rounded your result down, when rounding up is better.

At 96% free, Oracle will use ca. 0.04 * 8185 bytes which is 327 bytes - good for about 6 rows.

At 97% free this drops to 245, which allows 4 rows and just misses getting the 5th into the block.

One of the tools I use when I have to get fussy about packing data like this looks at the usage of columns within row - this can save a surprising amount of space __in special cases__. It reads dba_tab_columns for a table, then writes a script to convert null columns to spaces and non-null columns to hypens - concatenates the result to produce a string then does a sort group by and count on that string. Output looks like:

• ----- 199,206 --------------- - ----- 532.154 --------------- -- --- - 3,264,123

It can give you options for re-ordering columns to save space on trailing nulls, and some useful information about how the table is used.

I'll have to put it onto the web one of these days.

--

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

John Higgins wrote in message <377C316E.1CA169E9_at_deere.com>...
>But, can't we calculate a starting point based on his statement that the
rows are
>inserted as 50 bytes and are updated into thhe 2000 byte range?
>
>I think this means that PCTFREE needs to be set to a value that causes
Oracle to go to
>the next block as soon as 4 50 byte rows have been inserted! I compute this
as (8192 -
>107 - 4*50) / 8192 = 96%. PCTUSED would be 3%.
>
Received on Fri Jul 02 1999 - 02:51:40 CDT