Formula for table sizing

Calculate the average size (in bytes) of each column for the table depending on the data type.

NUMBER AvgSize = (PRECISION / 2) + 1)

CHAR AvgSize = DATA_LENGTH

DATE AvgSize = 7

VARCHAR2

DATA_LENGTH > 200 AvgSize = 20% of DATA_LENGTH

DATA_LENGTH > 50 AvgSize = DATA_LENGTH / 2

DATA_LENGTH > 10 AvgSize = 2/3 of DATA_LENGTH

DATA_LENGTH <= 10 AvgSize = DATA_LENGTH

OTHER DATATYPE AvgSize = DATA_LENGTH

what to do about LONG, LONG RAW or MLSLABEL data types?

Total the average size for each column to get the row size for the table

Add 3 bytes for the row header and 1 byte for each column in the table to get the true average row size for each table. (See page 8-21 Server Administrator's guide)

Calculate the average number of rows that can fit into a block using the following formula

[Rowsperblock] = 1768/([AvgRowSize]+2)
(See page 8-22 Server Administrator's guide based on default 10% PCTFREE)

Calculate the number of blocks required to hold the expected no. of records

[TotalBlocks] = [NumberofRecords]/[Rowsperblock]

Calculate the estimated initial extent

initial extent (K) = [TotalBlocks]*2.048

Calculate the next extent (K) as being 10% of the initial extent

Formula for index sizing

Calculate the average size for each column in the index using the method above.

Total the average size for each column to get the size for the index

Add 2 bytes for the entry header, 6 bytes for the ROWID and 1 byte for each column in the index to get the true average size for each index. (See page 8-37 Server Administrator's guide)

Calculate the estimated initial extent

initial extent (K) =([NumberofRecords]*[AvgSize]/1700)*2.048

Calculate the next extent (K) as being 10% of the initial extent

GFG Microsystems LtdGFG IT DevelopmentGFG ITGFG IT UK 99-FO-43/0.016 June 1998GFG Microsystems Ltd

GFG IT UK 99-FO-43/0.01	6 June 1998

1 FORMULA FOR TABLE SIZING

Formula for Table Sizing

Gerald McMullon

1998-07-07