|
-
Calculating exact rowsize.
I was reading one of the docs on metalink regarding this and phew.. it isn't that simple..
Posting the excerpts from Metalink...
----
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_ 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
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: 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
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) = MAX(UB1 * 3 + UB4 + SB2, rowsize) + SB2
Where:
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, 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.
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:
FLOOR rounds 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.
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
----
Sanjay
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|