Oracle7 Server Administrator's Guide

Contents Index Home Previous Next

Estimating Space Required by Non-Clustered Tables

The procedures in this section describe how to estimate the total number of data blocks necessary to hold data inserted into a non-clustered table Within this sample calculation, no concurrency is assumed, and users are not performing intervening delete or update operations.

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.

To Calculate Space Required by Non-Clustered Tables

Step 1: Calculate the Total Block Header Size

The space required by the data block header is the result of the following formula:

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

Where:

DB_BLOCK_ SIZEis the database block size as viewed in the V$PARAMETER view
KCBH, UB4, KTBBH, KTBIT,KDBHare constants whose sizes you can obtain by selecting from entries in the V$TYPE_SIZE view
INITRANSis the initial number of transaction entries allocated to the table

Step 2: Calculate the Available Data Space Per Data Block

The space reserved in each data block for data, as specified by PCTFREE, is calculated as follows:

available data space (availspace)
= 
CEIL(hsize * (1 - PCTFREE/100)) - KDBT

Where:

CEILrounds a fractional result to the next highest integer
PCTFREEis the percentage of space reserved for updates in the table
KDBTis 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

Calculating the amount of space used per row is a multi-step task.

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, SB2are 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

You can calculate the total number of rows that will fit into a data block using the following equation:

Number of rows in block
=
FLOOR(availspace / rowspace)

Where:

FLOORrounds 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

After a table is created and in use, the space required by the table is usually higher than the estimate derived from your calculations. More space is required due to the method by which Oracle manages free space in the database.


Contents Index Home Previous Next