Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
how to get length of long column?
- Thread starter rohit1346
- Start date
here's a formula to calculate average length from metalink Note:10640.1
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 directory where: 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 The 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) bytes Now, 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 bytes Calculating the average space required for the columns in an average row for table test: average row = (A + B + C) = (10 + 7 + 5) bytes = 22 bytes Note: 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 table F = 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 bytes Note: 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 block Make 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 block Using 10,000 rows for table test: number of blocks for table test = 10000 rows / 59 rows per block ~ 169 blocks To get this value in bytes, multiply by the database block size. And again, round the number of blocks from the above DOWN.
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 directory where: 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 The 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) bytes Now, 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 bytes Calculating the average space required for the columns in an average row for table test: average row = (A + B + C) = (10 + 7 + 5) bytes = 22 bytes Note: 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 table F = 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 bytes Note: 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 block Make 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 block Using 10,000 rows for table test: number of blocks for table test = 10000 rows / 59 rows per block ~ 169 blocks To get this value in bytes, multiply by the database block size. And again, round the number of blocks from the above DOWN.
- Thread starter
- #3
- Status