Estimating Space Required by Non-Clustered Tables

Note: This is a best case scenario only when users insert rows without performing deletes or updates.

Typically, the space required to store a set of rows will exceed this calculation when updates and deletes are also being performed on the table. The actual space required for complex workloads is best determined empirically, and then scaled by the number of rows in the table. In general, increasing amounts of concurrent activity on the same data block results in additional overhead (for transaction records), so it is important that you take into account such activity when scaling empirical results.

1. Calculate the total block header size.

2. Calculate the available data space per data block.

3. Calculate the space used per row.

4. Calculate the total number of rows that will fit in a data block.

Step 1: Calculate the Total Block Header Size

Space after headers (hsize) 
= 
DB_BLOCK_SIZE - KCBH - UB4 - KTBBH - (INITRANS - 1) * KTBIT - KDBH

Where:

DB_BLOCK_ SIZE	is the database block size as viewed in the V$PARAMETER view

KCBH, UB4, KTBBH, KTBIT,KDBH	are constants whose sizes you can obtain by selecting from entries in the V$TYPE_SIZE view

INITRANS	is the initial number of transaction entries allocated to the table

Step 2: Calculate the Available Data Space Per Data Block

available data space (availspace)

= 
CEIL(hsize * (1 - PCTFREE/100)) - KDBT

Where:

CEIL	rounds a fractional result to the next highest integer

PCTFREE	is the percentage of space reserved for updates in the table

KDBT	is a constant whose size you can obtain by selecting the entry from the V$TYPE_SIZE view

Note: If you are unable to locate the value of KDBT, use the value of UB4 instead.

Step 3: Calculate the Space Used per Row

First, you must calculate the column size, including byte lengths:

Column size including byte length

=

column size + (1, if column size < 250, else 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

Finally, you can calculate the space used per row:

Space used per row (rowspace)

=

MIN(UB1 * 3 + UB4 + SB2, rowsize) + SB2

Where:

UB1, UB4, SB2	are constants whose size can be obtained by selecting entries from the V$TYPE_SIZE view

When the space per row exceeds the available space per data block, but is less than the available space per data block without any space reserved for updates (for example, available space with PCTFREE=0), each row will be stored in its own block.

When the space per row exceeds the available space per data block without any space reserved for updates, rows inserted into the table will be chained into 2 or more pieces, hence, this storage overhead will be higher.

Figure A - 1 depicts elements in a table row.

Figure A - 1. Calculating the Size of a Row

Step 4: Calculate the Total Number of Rows That Will Fit in a Data Block

Number of rows in block

=

FLOOR(availspace / rowspace)

Where:

FLOOR	rounds a fractional result to the next lowest integer

In summary, remember that this procedure provides a reasonable estimate of a table's size, not an exact number of blocks or bytes. After you have estimated the size of a table, you can use this information when specifying the INITIAL storage parameter (size of the table's initial extent) in your corresponding CREATE TABLE statement.

See Also: See your operating system-specific Oracle documentation for any substantial deviations from the constants provided in this procedure.

Space Requirements for Tables in Use