Estimating Table and Index Sizes

As was pointed out in the preceding chapter, space management is a very critical issue and responsibility for DBAs. Although there are the very routine aspects of monitoring tablespace and disk space usage over time and making projections for the future based on that history, DBAs are also often asked to estimate how big an object will be merely based on the estimated row count. For example, how large will this table be if it had a million instead of ten thousand rows? TOAD provides estimate screens of just this nature for both tables and indexes.

The TOAD Table Size Estimator is accessible from the main menu at ToolsEstimate Table Size and offers three methods for estimating table sizes. First and by default, TOAD can use the table's structural information to calculate the average row size and then multiply that by the desired row count as shown in Figure 6.12. Note that this method provides overly large estimates because it assumes each row will use the entire potentially variable length for each record. Second, TOAD can use any pre-existing analysis information on the table as shown in Figure 6.13. TOAD simply multiplies the analyzed average row length by the desired row count, which provides a significantly higher degree of accuracy. Finally, TOAD itself can calculate the average row size by examining actual table data as shown in Figure 6.14. However, this method can take an inordinately long time and produce results that are only marginally more accurate.

Figure 6.12. TOAD Table Size Estimate ” structural.

Figure 6.13. TOAD Table Size Estimate ” analyzed.

Figure 6.14. TOAD Table Size Estimate ” calculated.

The TOAD Index Size Estimator is accessible from the main menu at ToolsEstimate Index Size and offers two methods for estimating table sizes. First and by default, TOAD can use the table's structural information to calculate the average row size and then multiply that by the desired row count as shown in Figure 6.15. Note that this method provides overly large estimates because it assumes each index column will use the whole potentially variable length. And second, TOAD itself can calculate the average row size by examining actual table data as shown in Figure 6.16. However, this method can take an inordinately long time and yet produces results that are only marginally more accurate.

Figure 6.15. TOAD Index Size Estimate ”structural.

Figure 6.16. TOAD Index Size Estimate ”calculated.

There is only one caveat for both these screens and all their various flavors. TOAD assumes relatively simple table and index structures upon which to base these estimates. Although both these screens will function on objects containing newer data types and complex features such as index organized tables, bitmap indexes, compressed tables, and the like, the results may nonetheless be far less accurate. In other words these screens work great for simple tables and indexes, but require improvements for these newer feature-based scenarios. Look for these enhancements in TOAD 7.5 or higher because space management is truly a critical DBA function.