看文档 C - TABLES ======================================================================= How will you estimate the space required by a non-clustered tables?Calculate the total header size Calculate the available dataspace per data block Calculate the combined column lengths of the average row Calculate the total average row size. Calculate the average number rows that can fit in a block Calculate the number of blocks and bytes required for the table. =======================================================================Table size increases proportionally with the number of rows in the table assuming average row length remains constant. While the DBA Guide provides a formula for calculating table size, it may not be as accurate as the following formula when calculating space for tables with a large number of records. Below is a formula that will provide more accurate results for both small and large tables.Note that this procedure estimates only the initial amount of space required for the table. Space used by transaction entries and deleted entries does not become free immediately after the commit due to delayed cleanout. Trailing nulls are not stored in the database.Below are methods for calculating space for tables. These are approximations, so it is a good idea to round up. Also making the initial extent size 10 to 20 percent larger than the value from these calculations may avoid allocating additional extents right away. Some values may be operating system dependent.Knowing the type of transactions that will affect the data in the tables is also very important. This will help you size pctfree and pctused accordingly.For example, as the rows lengthen in a block, row chaining may result if pctfree is not set sufficiently high. Also deletes and updates may empty out blocks so that the table needs more blocks even though the number of rows in the table is static. For details on pctfree and pctused, please refer to the Oracle RDBMS Database Administrator's Guide for Version 7/ Version 8i.First, calculate the total block header size:block header = fixed header + variable transaction header + table directory + row directorywhere: fixed header = 57 bytes (the 4 bytes at the end of the block have already been taken account of in the 24 bytes for the cache header)variable transaction header = 23 * i where i is the value of INITRANS for the table, or can grow dynamically up to MAXTRANS.table directory = 4 * n where n is the number of tables. n = 1 for non-clustered tables.row directory = 2 * x where x is the number of rows in the block.The variable transaction header in the block header contains 23 byte-structures that hold information about transactions accessing the block. By default, space is reserved for only one transaction (INITRANS = 1) in the block header. As more transactions access the block, space for additional transaction entries is allocated out of the free space in the block or an old entry is reused. As rows are inserted, the 2 bytes for each row in the row directory are also allocated out of the free space. Once allocated, this space becomes part of the block header. So, once the two bytes have been allocated in the row directory, this space is not reclaimed when the row is deleted. For example, a block that is currently empty but had up to 50 rows at one time will have 100 bytes allocated in the header for the row directory. This space will be reused as new rows are inserted in the block.Using the above formula, the initial block header size for a non-clustered table with INITRANS = 1 is:block header = 57 + 23 + 4 + 2x = (84 + 2x) bytes 2The space reserved for data within the block, as specified by PCTFREE, is calculated as a percentage of the block size minus the block header.available data space = (block size - total block header) - ((block size - total block header) * (PCTFREE/100))For example, with PCTFREE = 10 and a block size of 2048, the total space for new data in a block is:available data space = (2048 - (84 + 2x)) - ((2048 - (84 + 2x)) * (10/100)) = (1964 - 2x) - ((2048 - 84 - 2x) * (10/100)) = (1964 - 2x) - (1964 - 2x) * 0.1 = (1964 - 2x - 196 + 0.2x) bytes = (1768 - 1.8x) bytesNow, calculate the combined data space required for an average row. Calculating this depends on the following:1. The number of columns in the table definition. 2. The datatypes used for each column. 3. The average value size for variable length columns.A test database similar to the production database will be helpful here. To calculate the combined data space for an average row in a table, use the following query:SELECT AVG(NVL(VSIZE(col1), 1)) + AVG(NVL(VSIZE(col2), 1)) + ... + AVG(NVL(VSIZE(coln), 1)) "SPACE OF AVERAGE ROW" FROM table_name;col1, col2, ... , coln are the column names of the table and table_name is the table being evaluated.Note: This formula assumes that columns containing nulls are not trailing columns. A column length of 1 is assumed (column length of a null in a trailing column is 0).For example, a test table created with the following statement:CREATE TABLE test ( A CHAR(10), B DATE, C NUMBER(10, 2));can have the space determined by the average row in the table with the query:SELECT AVG(NVL(VSIZE(A), 1)) + AVG(NVL(VSIZE(B), 1)) + AVG(NVL(VSIZE(C), 1)) "SPACE OF AVERAGE ROW" FROM test;Alternatively, if a test database is not available, you can estimate the combined data space for an average row in a table. This is done by examining the datatype of each column in a table. If the column is of a fixed length datatype, the value for the column in each row of the table is fixed. For variable length columns, you must determine the average column value and determine how much space is required to hold this average value.For example, using the above table test, the column lengths of the first two columns are of fixed lengths. So, column A requires 10 bytes (assuming a one character set is being used) and column B requires 7 bytes. Column C can vary for each row in the table because the NUMBER datatype is a variable length datatype. By estimating your proposed data, you can determine the typical value for this column. For NUMBER datatypes, a typical value may require approximately:average length column C = (average_precision/2 + 1) bytes = (8/2 + 1) bytes (estimating an average_precision of 8) = 5 bytesCalculating the average space required for the columns in an average row for table test:average row = (A + B + C) = (10 + 7 + 5) bytes = 22 bytesNote: negative numbers consume one additional byte for the sign.Using the results from above, we can calculate the total average row size (or the minimum amount of space required by a row) in a non-clustered table with the formula:average row size = row header + F + V + D (bytes per row)where: row header = 3 bytes per row of a non-clustered tableF = total length bytes of all columns with 1 byte column lengths (CHAR, NUMBER, DATE, and ROWID types)V = total length bytes of all columns with 3 byte column lengths (VARCHAR2, LONG, RAW, LONG RAW datatypes)D = combined data space of all columns in average row (from above)For example, the total average row size for table test is:average row size = (3 + (3 * 1) + (3 * 0) + 22) bytes = 28 bytesNote: The minimum row size for a non-clustered table is 9 bytes. Therefore, if the calculated value for an average row size is less than this absolute minimum row size, use the minimum value as the average row size.
After the average row size is determined, you can calculate the average number of rows that can fit into a database block (the values of PCTFREE and PCTUSED will also determine the amount of space used):average number of rows per block =floor(available data space / average row size)where: available data space and average row size are calculated above.Using the information for table test from above:average number of rows per block = x = (1768 - 1.8x)/28 bytes 28x = 1768 - 1.8x 29.8x = 1768 x ~ 59 = average number of rows per blockMake sure you round x or the average number of rows per block DOWN. Once you know the number of rows that can fit inside the available space of a database block, you can calculate the number of blocks required to hold the proposed table:number of blocks for the table = number of rows / average number of rows per blockUsing 10,000 rows for table test:number of blocks for table test = 10000 rows / 59 rows per block ~ 169 blocksTo get this value in bytes, multiply by the database block size. And again, round the number of blocks from the above DOWN. D - INDEXES ============Indexes increase in size slightly faster than the corresponding table. So, you should round up when estimating index size. Formulas are provided below for calculating the size of an index:First, calculate the space required by the database block header of a block to contain index data. To calculate the total block header size:block header size = fixed header + variable transaction headerwhere: fixed header = 113 bytesvariable transaction header = 23*i where i is the value of INITRANS for the indexAssuming the INITRANS = 2 (the default for indexes), the previous formula can be reduced to:block header size = 113 + (23 * 2) bytes = 159 bytesThe space reserved in each database block for index data, as specified by PCTFREE, is calculated as a percentage of the block size minus the block header.available data space = (block size - block header size) - ((block size - block header size) * (PCTFREE/100))Assuming a block size of 2048 bytes and PCTFREE of 10:available data space = (2048 bytes - 159 bytes) - ((2048 bytes - 159 bytes) * (10/100)) = 1889 bytes - 188.9 bytes = 1700.1 bytesThe calculation of the combined column lengths of an average index value is the same as the calculation for table size, except you only need to calculate the average combined column lengths of the columns in the index. This formula is listed above.Once the combined column length of an average index entry has been calculated, the total average entry size can be calculated, using the formula:bytes per entry = entry header + ROWID length + F + V + Dwhere: entry header = 1 byteROWID length = 6 bytesF = total length bytes of all columns with 1 byte column lengths (CHAR, NUMBER, DATE, and ROWID types)V = total length bytes of all columns with 3 byte column lengths (VARCHAR2 and RAW datatypes)D = combined data space of all columns (from above)For example, given that D is calculated to be 22 bytes (from the table calculations above), and that the index is comprised of three CHAR columns, the total average entry size of the index is:bytes per entry = 1 + 6 + (3 * 1) + (3 * 0) + 22 bytes = 32 bytesTo calculate the number of blocks and bytes required for the index, use:number of blocks for index = 1.1 * (number of not null rows /floor (avail. data space / avg. entry size)) = 1.1 * ((number of not null rows * avg. entry size) / avail. data space)Note: The additional 10% added to this result accounts for the extra space required for branch blocks of the index.number of blocks for index = 1.1 * ((10000 * 32 bytes) / ((floor (1700/32 bytes)) * (32 bytes)) = 208 blocksThe number of bytes can be calculated by multiplying the result by the database block size. This method for indexes is more approximate than for tables.An index, unlike a table, does not contain rows. Therefore, no query similar to the table query above exists for indexes. Also, the VSIZE function will not report information about index storage space.Putting indexes for large tables in their own tablespaces makes space management more efficient. It decreases fragmentation and also makes managing the index growth easier if recreating the index is necessary.
现在没有数据，以后才有数据的话，楼上的方法就可以了。想查看现在占用的空间，可以：SELECT TABLESPACE_NAME,TO_CHAR(SUM(BYTES)/(1024*1024),'999G999D999') CNT_MB FROM DBA_EXTENTS WHERE OWNER='&OWNER' AND SEGMENT_NAME='&TABLE_NAME' AND SEGMENT_TYPE LIKE 'TABLE%' GROUP BY TABLESPACE_NAME;
C - TABLES
=======================================================================
How will you estimate the space required by a non-clustered tables?Calculate the total header size
Calculate the available dataspace per data block
Calculate the combined column lengths of the average row
Calculate the total average row size.
Calculate the average number rows that can fit in a block
Calculate the number of blocks and bytes required for the table.
=======================================================================Table size increases proportionally with the number of rows in the table
assuming average row length remains constant. While the DBA Guide provides a
formula for calculating table size, it may not be as accurate as the following
formula when calculating space for tables with a large number of records.
Below is a formula that will provide more accurate results for both small and
large tables.Note that this procedure estimates only the initial amount of space required
for the table. Space used by transaction entries and deleted entries does not
become free immediately after the commit due to delayed cleanout. Trailing
nulls are not stored in the database.Below are methods for calculating space for tables. These are approximations,
so it is a good idea to round up. Also making the initial extent size 10 to
20 percent larger than the value from these calculations may avoid allocating
additional extents right away. Some values may be operating system dependent.Knowing the type of transactions that will affect the data in the tables is
also very important. This will help you size pctfree and pctused accordingly.For example, as the rows lengthen in a block, row chaining may result if
pctfree is not set sufficiently high. Also deletes and updates may empty out
blocks so that the table needs more blocks even though the number of rows in
the table is static. For details on pctfree and pctused, please refer to
the Oracle RDBMS Database Administrator's Guide for Version 7/ Version 8i.First, calculate the total block header size:block header = fixed header + variable transaction header +
table directory + row directorywhere:
fixed header = 57 bytes (the 4 bytes at the end of the block have
already been taken account of in the 24 bytes
for the cache header)variable transaction header = 23 * i where i is the value of INITRANS
for the table, or can grow
dynamically up to MAXTRANS.table directory = 4 * n where n is the number of tables.
n = 1 for non-clustered tables.row directory = 2 * x where x is the number of rows in the block.The variable transaction header in the block header contains 23 byte-structures
that hold information about transactions accessing the block. By default,
space is reserved for only one transaction (INITRANS = 1) in the block header.
As more transactions access the block, space for additional transaction
entries is allocated out of the free space in the block or an old entry is
reused. As rows are inserted, the 2 bytes for each row in the row directory
are also allocated out of the free space. Once allocated, this space becomes
part of the block header. So, once the two bytes have been allocated in the
row directory, this space is not reclaimed when the row is deleted. For example,
a block that is currently empty but had up to 50 rows at one time will have 100
bytes allocated in the header for the row directory. This space will be reused
as new rows are inserted in the block.Using the above formula, the initial block header size for a non-clustered
table with INITRANS = 1 is:block header = 57 + 23 + 4 + 2x = (84 + 2x) bytes 2The space reserved for data within the block, as specified by PCTFREE, is
calculated as a percentage of the block size minus the block header.available data space = (block size - total block header) -
((block size - total block header) * (PCTFREE/100))For example, with PCTFREE = 10 and a block size of 2048, the total space for
new data in a block is:available data space = (2048 - (84 + 2x)) - ((2048 - (84 + 2x)) * (10/100))
= (1964 - 2x) - ((2048 - 84 - 2x) * (10/100))
= (1964 - 2x) - (1964 - 2x) * 0.1
= (1964 - 2x - 196 + 0.2x) bytes
= (1768 - 1.8x) bytesNow, calculate the combined data space required for an average row. Calculating
this depends on the following:1. The number of columns in the table definition.
2. The datatypes used for each column.
3. The average value size for variable length columns.A test database similar to the production database will be helpful here. To
calculate the combined data space for an average row in a table, use the
following query:SELECT AVG(NVL(VSIZE(col1), 1)) +
AVG(NVL(VSIZE(col2), 1)) +
... +
AVG(NVL(VSIZE(coln), 1)) "SPACE OF AVERAGE ROW"
FROM table_name;col1, col2, ... , coln are the column names of the table and table_name is the
table being evaluated.Note: This formula assumes that columns containing nulls are not trailing
columns. A column length of 1 is assumed (column length of a null in a
trailing column is 0).For example, a test table created with the following statement:CREATE TABLE test (
A CHAR(10),
B DATE,
C NUMBER(10, 2));can have the space determined by the average row in the table with the query:SELECT AVG(NVL(VSIZE(A), 1)) +
AVG(NVL(VSIZE(B), 1)) +
AVG(NVL(VSIZE(C), 1)) "SPACE OF AVERAGE ROW"
FROM test;Alternatively, if a test database is not available, you can estimate the
combined data space for an average row in a table. This is done by examining
the datatype of each column in a table. If the column is of a fixed length
datatype, the value for the column in each row of the table is fixed. For
variable length columns, you must determine the average column value and
determine how much space is required to hold this average value.For example, using the above table test, the column lengths of the first two
columns are of fixed lengths. So, column A requires 10 bytes (assuming a one
character set is being used) and column B requires 7 bytes. Column C can vary
for each row in the table because the NUMBER datatype is a variable length
datatype. By estimating your proposed data, you can determine the typical value
for this column. For NUMBER datatypes, a typical value may require approximately:average length column C = (average_precision/2 + 1) bytes
= (8/2 + 1) bytes (estimating an average_precision of 8)
= 5 bytesCalculating the average space required for the columns in an average row for
table test:average row = (A + B + C)
= (10 + 7 + 5) bytes
= 22 bytesNote: negative numbers consume one additional byte for the sign.Using the results from above, we can calculate the total average row size (or
the minimum amount of space required by a row) in a non-clustered table with
the formula:average row size = row header + F + V + D (bytes per row)where:
row header = 3 bytes per row of a non-clustered tableF = total length bytes of all columns with 1 byte column
lengths (CHAR, NUMBER, DATE, and ROWID types)V = total length bytes of all columns with 3 byte column
lengths (VARCHAR2, LONG, RAW, LONG RAW datatypes)D = combined data space of all columns in average row
(from above)For example, the total average row size for table test is:average row size = (3 + (3 * 1) + (3 * 0) + 22) bytes
= 28 bytesNote: The minimum row size for a non-clustered table is 9 bytes. Therefore, if
the calculated value for an average row size is less than this absolute
minimum row size, use the minimum value as the average row size.
of rows that can fit into a database block (the values of PCTFREE and PCTUSED
will also determine the amount of space used):average number of rows per block =floor(available data space / average row size)where:
available data space and average row size are calculated above.Using the information for table test from above:average number
of rows per block = x = (1768 - 1.8x)/28 bytes
28x = 1768 - 1.8x
29.8x = 1768
x ~ 59 = average number of rows per blockMake sure you round x or the average number of rows per block DOWN. Once you
know the number of rows that can fit inside the available space of a database
block, you can calculate the number of blocks required to hold the proposed
table:number of blocks
for the table = number of rows / average number of rows per blockUsing 10,000 rows for table test:number of blocks
for table test = 10000 rows / 59 rows per block
~ 169 blocksTo get this value in bytes, multiply by the database block size. And again,
round the number of blocks from the above DOWN.
D - INDEXES
============Indexes increase in size slightly faster than the corresponding table. So, you
should round up when estimating index size. Formulas are provided below for
calculating the size of an index:First, calculate the space required by the database block header of a block to
contain index data. To calculate the total block header size:block header size = fixed header + variable transaction headerwhere:
fixed header = 113 bytesvariable transaction header = 23*i where i is the value of INITRANS
for the indexAssuming the INITRANS = 2 (the default for indexes), the previous formula can
be reduced to:block header size = 113 + (23 * 2) bytes
= 159 bytesThe space reserved in each database block for index data, as specified by PCTFREE,
is calculated as a percentage of the block size minus the block header.available data space = (block size - block header size) -
((block size - block header size) * (PCTFREE/100))Assuming a block size of 2048 bytes and PCTFREE of 10:available data space = (2048 bytes - 159 bytes) -
((2048 bytes - 159 bytes) * (10/100))
= 1889 bytes - 188.9 bytes
= 1700.1 bytesThe calculation of the combined column lengths of an average index value is the
same as the calculation for table size, except you only need to calculate the
average combined column lengths of the columns in the index. This formula is
listed above.Once the combined column length of an average index entry has been calculated,
the total average entry size can be calculated, using the formula:bytes per entry = entry header + ROWID length + F + V + Dwhere:
entry header = 1 byteROWID length = 6 bytesF = total length bytes of all columns with 1 byte column
lengths (CHAR, NUMBER, DATE, and ROWID types)V = total length bytes of all columns with 3 byte column
lengths (VARCHAR2 and RAW datatypes)D = combined data space of all columns (from above)For example, given that D is calculated to be 22 bytes (from the table
calculations above), and that the index is comprised of three CHAR columns, the
total average entry size of the index is:bytes per entry = 1 + 6 + (3 * 1) + (3 * 0) + 22 bytes
= 32 bytesTo calculate the number of blocks and bytes required for the index, use:number of blocks
for index = 1.1 * (number of not null rows /floor (avail. data space / avg. entry size))
= 1.1 * ((number of not null rows * avg. entry size) / avail. data space)Note: The additional 10% added to this result accounts for the extra space
required for branch blocks of the index.number of blocks
for index = 1.1 * ((10000 * 32 bytes) / ((floor (1700/32 bytes)) *
(32 bytes))
= 208 blocksThe number of bytes can be calculated by multiplying the result by the database
block size. This method for indexes is more approximate than for tables.An index, unlike a table, does not contain rows. Therefore, no query similar
to the table query above exists for indexes. Also, the VSIZE function will not
report information about index storage space.Putting indexes for large tables in their own tablespaces makes space
management more efficient. It decreases fragmentation and also makes managing
the index growth easier if recreating the index is necessary.
FROM DBA_EXTENTS
WHERE OWNER='&OWNER' AND SEGMENT_NAME='&TABLE_NAME' AND SEGMENT_TYPE LIKE 'TABLE%'
GROUP BY TABLESPACE_NAME;