Re: Table Sizing

Not really - you have to take into account the average size of the rows and the percent free in the block.

To work out the initial size to set the table at, you need to work out approximately how many rows the table will have when in production. Then you need to know how many rows can fit into a block (leaving some room to grow), and that will be the number of blocks you will need to set aside for the table when creating it.

As an example, let's say we have a block size of 2048 bytes (which is standard). A block header will take around 90 bytes. If we are leaving 10% of the block free for the rows to grow, then we need 10% of (2048 - 90) which is 196 bytes for our free space, leaving 1762 bytes available for the rows. The average space that a row will take up will be the average row length (the sum of all the average field lengths) plus 3 bytes for the row header plus a byte per column and another byte per column greater than 250 characters in length. The number of rows that can fit into a block is the space available divided by the average row size. Thus, the initial table size should be set to the number of rows divided by the rows per block multiplied by the block size.

For existing tables, the number of rows per block can be worked out by the following pieces of SQL:
Analyze table 'YourTableNameHere' compute statistics;

select num_rows, blocks, num_rows/blocks from user_tables
where table_name = 'YourTableNameHere';

A similar strategy applies to sizing indexes.

Christopher Latta

Jen Decker <jen_at_sunflower.com> wrote in message news:B7575B86.2C27%jen_at_sunflower.com...
> The simple solution is to multiply the row length by the number of rows.
> The same can be figured for the index, multiply the column(s) length by
 the
> number of rows.
> Jen
Received on Thu Jun 21 2001 - 21:53:42 CDT