|Oracle7 Server Administrator's Guide||
Attention: While these equations help estimate schema object size, they are approximations, and may vary from your actual results
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
Space after headers (hsize) = DB_BLOCK_SIZE - KCBH - UB4 - KTBBH - (INITRANS - 1) * KTBIT - KDBH
|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|
available data space (availspace)
= CEIL(hsize * (1 - PCTFREE/100)) - KDBT
|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|
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:
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
|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 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
Number of rows in block
FLOOR(availspace / rowspace)
|FLOOR||rounds a fractional result to the next lowest integer|
See Also: See your operating system-specific Oracle documentation for any substantial deviations from the constants provided in this procedure.
The calculations in the procedure rely on average column lengths of the columns that constitute an index; therefore, if column lengths in each row of a table are relatively constant with respect to the indexed columns, the estimates calculated by the following procedure are more accurate.
To Estimate Space for Indexes
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 Also: See your operating system-specific Oracle documentation for any substantial deviations from the constants provided in the following procedure.
block header size = fixed header + variable transaction header
|fixed header* 113 bytes|
|variable transaction header* 24*I I is the value of INITRANS for the index.|
block header = 113 + (24*2) bytes
= 161 bytes
Figure A - 2. Calculating the Space for an Index
data = (block size - block header) -
space per block ((block size - block header)*(PCTFREE/100))
The block size of a database is set during database creation and can be determined using the Server Manager command SHOW, if necessary:
SHOW PARAMETERS db_block_size;
If the data block size is 2K and PCTFREE=10 for a given index, the total space for new data in data blocks allocated for the index is:
available data space per block
= (2048 bytes - 161 bytes) -
((2048 bytes - 161 bytes)*(10/100))
= (1887 bytes) - (1887 bytes * 0.1)
= 1887 bytes - 188.7 bytes
= 1698.3 bytes
bytes/entry = entry header + ROWID length + F + V + D
|entry header 2 bytes|
|ROWID length 6 bytes|
|F Total length bytes of all columns that store 127 bytes or less. The number of length bytes required by each column of this type is 1 byte.|
|V Total length bytes of all columns that store more than 127 bytes. The number of length bytes required by each column of this type is 2 bytes.|
|D Combined data space of all index columns (from Step 3).|
For example, given that D is calculated to be 22 bytes and that the index is comprised of three VARCHAR(10) columns, the total average entry size of the index is:
avg. entry size = 2 + 6 + (1 * 3) + (2 * 0) + 22 bytes
= 33 bytes
Note: For a non-unique index, the ROWID is considered another column, so it must have one length byte.
# blocks for index =
# not null rows
1.05 * _________________________________________________
FLOOR(avail. data space per block/avg. entry size)
Note: The additional 5% added to this result (by means of the multiplication factor of 1.05) accounts for the extra space required for branch blocks of the index.
For example, continuing with the previous example, and assuming you estimate that indexed table will have 10000 rows that contain non-null values in the columns that constitute the index:
# blocks for index =
10000 * 33 bytes
1.05 * _____________________________________
FLOOR(1700 bytes/33 bytes)*(33 bytes)
This results in 204 blocks. The number of bytes can be calculated by multiplying the number of blocks by the data block size.
Remember that this procedure provides a reasonable estimate of an index's size, not an exact number of blocks or bytes. Once you have estimated the size of a index, you can use this information when specifying the INITIAL storage parameter (size of the index's initial extent) in your corresponding CREATE INDEX statement.
Note: Temporary space is not required if the NOSORT option is included in the CREATE INDEX command. However, you cannot specify this option when creating a cluster index.
To Estimate Space Required by Clusters
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
|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|
Use Step 3 from the procedure in "Calculating Space Required by Non-Clustered Tables" to calculate this number. Make note of the following caveats:
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 (D1) of table T1 and the space required for an average row (D2) of table T2 is:
D1 (space/average row) = (a + b)
= (10 + 7) bytes
= 17 bytes
D2 (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:
Sn bytes/row = row header + Fn + Vn + Dn
|row header* 4 bytes per row of a clustered table.|
|Fn 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.|
|Vn 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.|
|Dn Combined data space of all columns in table n (from Step 3).|
For example, the total average row size of the clustered tables T1 and T2 are as follows:
S1 = (4 + (1 * 2) + (3 * 0) + 17) bytes
= 23 bytes
S2 = (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)=
((R1*S1) + (R2*S2) + .. + (Rn*Sn)) + key header + Ck + Sk + 2Rt
|Rn The average number of rows in table n associated with a cluster key.|
|Sn The average row size in table n (see Step 4).|
|key header* 19|
|Ck Column length for the cluster key.|
|Sk Space required to store average cluster key value.|
|Rt Total number of rows associated with an average cluster key (R1 + R2 ... + Rn). This accounts for the space required in the data block header for each row in the block.|
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 (Rt), and SIZE:
# cluster keys per block = FLOOR(available data space + 2R / SIZE + 2Rt)
For example, with SIZE previously calculated as 400 bytes (calculated as 388 earlier in this step and rounded up), Rt 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)
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)
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.
Copyright © 1996 Oracle Corporation.
All Rights Reserved.