This articles explains you about estimating the space required for a new table or the space required for an existing table. To estimate the space required by a table , following are necessary:
- The average length of a row
- The expected number of rows in the new table or an existing table.
- The Database Block size.
- The Pctfree value for a table.
Data is stored logically in the form of blocks. A database block is the smallest logical building block of a database. Each block can store some data based on its size specified for the tablespace or database as a whole.
A block header is an area used for overhead within the block. Estimate the block overhead for tables to use 90 bytes. Based on the block size we can calculate the available space for each database block size as The pctfree setting for the table sets the size of the free space that is unused during inserts. Multiply the available space by the pctfree value to determine how much space is reserved. Subtract that value from the available space in the block to determine how much space is available to hold newly inserted rows.
2Kb Block size Actual space (in bytes) -- 2048Available Space (in bytes) -- 1958
Percent Available -- 96%
4Kb Block sizeActual space (in bytes) -- 4096Available Space (in bytes) -- 4006
Percent Available -- 98%
8Kb Block sizeActual space (in bytes) -- 8192Available Space (in bytes) -- 8102
Percent Available -- 99%
For example for a 8kb Block size and a table with a pctfree setting of 20 (high updates), the available space is
8192-(0.2 * 8192)= 6553 Bytes
To estimate the space required for a new table, you should generate sample data and analyze its storage. Analyzing an accurate sample of 3000 rows should tell you the average length of a row. If you are unable to generate the sample data for your new table, you can calculate the near by row length by calculating the sizes of each column of the table.
For example if you have a table with one date column , two number columns, two varchar2 columns.
Then the calculation should be as follows
Date datatype: A date value requires 7 Bytes to store the data.
Number Datatype: A column with Number Value’s Stored length depends on its number of significant digits. Internal storage for Number Value varies from 2 bytes( 2 or 3 Significant digits) to 20 bytes (35 Significant digits).
Varchar2 Datatype : A varchar2 Column requires the storage for the actual no of characters in that column.
Formula for calculating the space
(Avg_row_length * No of rows)/( Storage space available in block * storage space after removing the block header)
May 19th, 2008 at 2:17 pm
I want create a new table and block size is 8 KB, avg row length is 130 an No of rows is 1000000. How estimate size this table ?
May 20th, 2008 at 5:39 am
Hi Marcello
By the above calculation you required 122.7Mb for the above table. You can add 20 to 30 Mb for buffer. So the total size constitutes to 142 Mb
July 3rd, 2008 at 12:02 am
I followed your example and using the formula at the bottom of your article I got a very small number, not anything close to 122.7.
IE (8k block size, pctfree=20%):
(130*1000000)/(6553*8102)=2.45
July 3rd, 2008 at 1:29 am
Hi Marcello
I am sorry, may be i have confused you. something is missing in my post, thanks for correcting me.
What i mean to say in the above statement is
(Avg_row_length * No of rows)/( Storage percentage space available in block * storage percentage space after removing the block header)
if you block size is 8k the formula can be
(avg_row_length * no_of_rows)/((100-Pct_free)/100) * ((v_block_size_in_bytes-90)/v_block_size_in_bytes)
(130 * 1000000)/((100-20)/100) * ((8192-90)/8192)
Please do read my post on estimate existing table size for future expansion where i have also created a Pl/sql anonymous block to estimate the table size.
Thank you for commenting with great regards
October 9th, 2008 at 6:53 am
a. The company offers 50 different items
b. The company has 20 sales persons
c. Each person sells on average 2500 products per year
d. There are three warehouses to ship sold products to the customer
e. Each warehouse can stock all products
f. Each sale creates a shipping order to a warehouse
g. The product has a product number (char(8)), a description
(varchar2(100), and a serial num-ber (char(12)), and a price (number(5,2))
h. The salesperson has an id (char(8)), a first name (varchar2(15)), and a surname (varchar2(30))
i. The warehouse has a location (varchar2(10)) and an address (varchar2(50)
j. Sales and shipment orders must have date and time recorded
Provide a reasonable estimate in megabytes for a database
• that can hold those data, under the condition that the original database will be loaded at no more than 50% of available space (with data from the past year)
• and there is the assumption that the database size will double every 6 months.
• and the database should still be efficient (i.e. free space no less than 20%) after one year
List and explain, what kind of additional assumptions you make, to calculate your estimate.
1. An initial estimate for the size of “raw” data – i.e. no overhead included
2. A estimate for overhead approximately: each row needs a db-key (2 bytes), each variable-length field needs a counter (1 byte), each key needs an index ((2 bytes + key-length bytes) * (2 * number of keys), other overhead (such as db-block header & footer) approximately 1% of raw size.
3. An estimate for the total space requirement for the database