Oracle7 Server Administrator's Guide

Estimating Space Required by Clusters

The following procedure demonstrates how to estimate the initial amount of space required by a set of tables in a cluster. This procedure estimates only the initial amount of space required for a cluster. When using these estimates, note that the following items can affect the accuracy of estimations:

Trailing nulls are not stored, nor is a length byte.

Inserts of, updates to, and deletes of rows, as well as tables containing columns larger than a single data block can cause fragmentation and chained row pieces. Therefore, the following estimates may tend to be lower that the actual space required if significant fragmentation occurs.

Once you calculate a table's size using the following procedure, you should add about 10 to 20 percent additional space to calculate the initial extent size for a working table.

To Estimate Space Required by Clusters

1. Calculate total block header size and space available for table data.

2. Calculate the combined column lengths of the average rows per cluster key.

3. Calculate the average row size of all clustered tables.

4. Calculate the average cluster block size.

5. Calculate the total number of blocks required for the cluster.

Step 1 : Calculate Total Block Header Size and Space Available for Table Data

The following formula returns the amount of available space in a block:

Note: Several calculations are required to obtain a final estimate, and several of the constants (indicated by *) provided are operating system-specific. Your estimates should not significantly differ from actual values. See your operating system-specific Oracle documentation for any substantial deviations from the constants provided in the following procedure.

space left in block after headers (hspace)
=  BLOCKSIZE - KCBH - UB4 - KTBBH - KTBIT*(INITTRANS - 1) - KDBH

where the sizes of KCBH, KTBBH, KTBIT, KDBH, and UB4 can be obtained by selecting * from v$type_size table.

Note: If this is a table segment (instead of the cluster segment shown above), the table directory would simply be 4.

Then use the following formula to calculate the space available for table data:

space available for table data

= hspace*(1 - PCTFREE/100) - 4*(NTABLES + 1) * ROWSINBLOCK

where:

BLOCKSIZE is the size of a data block

INITTRANS is the initial number of transaction entries for the object

PCTFREE is the percentage of space to reserve in a block for updates

NTABLES is the number of tables in the cluster

ROWS INBLOCK is the number of rows in a block
Step 2 : Calculate Space Required by a Row

Use Step 3 from the procedure in "Calculating Space Required by Non-Clustered Tables" to calculate this number. Make note of the following caveats:

Calculate the data space required by an average row for each table in the cluster. For example, in a cluster that contains tables T1 and T2, calculate the average row size for both tables.

Do not include the space required by the cluster key in any of the above calculations. However, make note of the space required to store an average cluster key value for Step 5. For example, calculate the data space required by an average row in table T1, not including the space required to store the cluster key.

Do not include any space required by the row header (that is, the length bytes for each column); this space is accounted for in the next step.

For example, assume two clustered tables are created with the following statements:

CREATE TABLE t1 (a CHAR(10), b DATE, c NUMBER(10,2))

   CLUSTER t1_t2 (c);

CREATE TABLE t2 (c NUMBER(10,2), d CHAR(10))

   CLUSTER t1_t2 (c);

Notice that the cluster key is column C in each table.

Considering these example tables, the space required for an average row (D₁) of table T1 and the space required for an average row (D₂) of table T2 is:

D₁ (space/average row)	  = (a + b)

                      	  = (10 + 7) bytes

                      	  = 17 bytes

D₂ (space/average row)	  = (d)

                   	  = 10 bytes

Step 3 : Calculate Total Average Row Size

You can calculate the minimum amount of space required by a row in a clustered table according to the following equation:

S_n bytes/row = row header + F_n + V_n + D_n

where:

row header^* 4 bytes per row of a clustered table.

F_n Total length bytes of all columns in table n that store 250 bytes or less. The number of length bytes required by each column of this type is 1 byte.

V_n Total length bytes of all columns in table n that store more than 250 bytes. The number of length bytes required by each column of this type is 3 bytes.

D_n Combined data space of all columns in table n (from Step 3).
Note: Do not include the column length for the cluster key in variables F or V for any table in the cluster. This space is accounted for in Step 5.

For example, the total average row size of the clustered tables T1 and T2 are as follows:

   S₁   	= (4 + (1 * 2) + (3 * 0) + 17) bytes

        	= 23 bytes

   S₂   	= (4 + (1 * 1) + (3 * 0) + 10) bytes

       	= 15 bytes

Note: The absolute minimum row size of a clustered row is 10 bytes, and is operating system-specific. Therefore, if your calculated value for a table's total average row size is less than these absolute minimum row sizes, use the minimum value as the average row size in subsequent calculations.

Step 4 : Calculate Average Cluster Block Size

To calculate the average cluster block size, first estimate the average number of rows (for all tables) per cluster key. Once this is known, use the following formula to calculate average cluster block size:

avg. cluster block size (bytes)=

((R₁*S₁) + (R₂*S₂) + .. + (R_n*S_n)) + key header + C_k + S_k + 2R_t

where:

R_n The average number of rows in table n associated with a cluster key.

S_n The average row size in table n (see Step 4).

key header^* 19

C_k Column length for the cluster key.

S_k Space required to store average cluster key value.

R_t Total number of rows associated with an average cluster key (R₁ + R2 ... + R_n). This accounts for the space required in the data block header for each row in the block.
For example, consider the cluster that contains tables T1 and T2. An average cluster key has one row per table T1 and 20 rows per table T2. Also, the cluster key is of datatype NUMBER (column length is 1 byte), and the average number is 4 digits (3 bytes). Considering this information and the previous results, the average cluster key size is:

SIZE = ((1 * 23) + (20 * 15) + 19 + 1 + 3 + (2 * 21)) bytes

     = 388 bytes

Specify the estimated SIZE in the SIZE option when you create the cluster with the CREATE CLUSTER command. This specifies the space required to hold an average cluster key and its associated rows; Oracle uses the value of SIZE to limit the number of cluster keys that can be assigned to any given data block. After estimating an average cluster key SIZE, choose a SIZE somewhat larger than the average expected size to account for the space required for cluster keys on the high side of the estimate.

To estimate the number of cluster keys that will fit in a database block, use the following formula, which uses the value you calculated in Step 2 for available data space, the number of rows associated with an average cluster key (R_t), and SIZE:

# cluster keys per block
= FLOOR(available data space + 2R / SIZE + 2R_t)

For example, with SIZE previously calculated as 400 bytes (calculated as 388 earlier in this step and rounded up), R_t estimated at 21, and available space per data block (from Step 2) calculated as 1742 - 2R bytes, the result is as follows:

# cluster keys per block 
= FLOOR((1936 - 2R + 2R) / (400 + 2 * 21))

= FLOOR(1936 / 442)

= FLOOR(4.4)

= 4

Step 5 : Calculate Total Number of Blocks

To calculate the total number of blocks for the cluster, you must estimate the number of cluster keys in the cluster. Once this is estimated, use the following formula to calculate the total number of blocks required for the cluster:

# blocks = CEIL(# cluster keys / # cluster keys per block)

Note: If you have a test database, you can use statistics generated by the ANALYZE command to determine the number of key values in a cluster key. See "Analyzing Tables, Indexes, and Clusters" .

For example, assume that there are approximately 500 cluster keys in the T1_T2 cluster:

# blocks T1_T2 = CEIL(500/3)

               = CEIL(166.7)

               = 167

To convert the number of blocks to bytes, multiply the number of blocks by the data block size.

This procedure provides a reasonable estimation of a cluster's size, but not an exact number of blocks or bytes. Once you have estimated the space for a cluster, you can use this information when specifying the INITIAL storage parameter (size of the cluster's initial extent) in your corresponding CREATE CLUSTER statement.

Space Requirements for Clustered Tables in Use

Once clustered tables are created and in use, the space required by the tables is usually higher than the estimate given by the previous section. More space is required due to the method Oracle uses to manage free space in the database.