Sizing Table Space Empirically - Advanced SQL Engine - Teradata Database

The most accurate method for sizing table space for a new configuration is to create a table with a definition as similar as possible to the legacy table you want to port to Teradata Database. You cannot just extrapolate the space information from your legacy system and expect the result to accurately estimate your Teradata Database table space needs because commercial database vendors all store their data in different ways.

General Procedure

Use one of the load utilities to load the empty table with a representative sample of rows that constitutes a known percentage of the entire cardinality of the legacy table.

For the first iteration of testing, do not define any secondary indexes on the table. Analyze the space characteristics for this test table, add a secondary index, and repeat the task. Continue this process until you are reasonably certain that you have captured the characteristics of the base table and all the secondary indexes you initially want to define on it.

Extrapolate the total spool space required from an examination of the smaller temporary table. The scaled up data should provide a reliable picture of the initial space requirements for the table and its indexes.

Explicit Procedure

The following table provides a more explicit procedure:

1. Create a new base table on the Teradata platform with a definition that matches the definition for the legacy table it is replacing as closely as possible.

   Do not define any secondary indexes on this base table at this stage of the process.

2. Use one of the Teradata load utilities to load the newly defined base table with a representative sample of rows from the legacy table. The loaded sample should not only reflect the demographics of the entire population of the legacy table, but also be a fairly precise percentage of the cardinality for that table.
3. Query the system view (see Querying DBC.TableSizeV below) for the space occupied by the new base table.
4. Record the number of bytes returned by the query.
5. Add a secondary index to the table.
6. Query the system view again for the space occupied by the new base table and the new secondary index.
7. Record the number of bytes returned by the query.
8. The arithmetic difference between the numbers you recorded in step 4 and step 7 is the size of the newly defined secondary index.
9. Iterate step 5 and step 6 until you have finished adding all the secondary indexes you anticipate defining for this table.
10. Repeat the procedure with another legacy table until you have estimated the initial size of your entire Teradata database.
11. You should also evaluate the sizes of any hash or join indexes (and any secondary indexes defined on join indexes) you anticipate using with the new database. Because hash and join index tables are structurally virtually identical to base tables, you can use the same procedure and the same queries documented by this procedure to determine their size and the size of their indexes.

Querying DBC.TableSizeV

Use the following query to evaluate the sizing of your legacy tables. Assume the database in which the table was created is named employee_largetable and the name of the table is employee.

    SELECT SUM(CurrentPerm)
    FROM DBC.TableSizeV
    WHERE DatabaseName = ‘Employee_LargeTable’
    AND TableName = ‘Employee’;

The information returned might look something like this:

    Sum(CurrentPerm)
              2,527,232

Table Sizing Summary

The following list reviews the principal points to remember about estimating the table space requirements for your new database.

• Accurate estimates require accurate base data. The minimum base data required for these estimates are the following.
  • Cardinalities (or anticipated cardinalities) of your tables
  • Row size estimates

    Remember that all row lengths must be an even number of bytes (see Byte Alignment), so be sure to take this into account.

• Estimate sizes for all the following database objects.
  • Base tables
  • Base table fallback tables (when defined)
  • LOB and XML subtables
  • Secondary indexes
  • Secondary index fallback tables (when defined)
  • Hash indexes
  • Hash index fallback tables (when defined)
  • Join indexes (including any secondary indexes defined on them)
  • Join index fallback tables (when defined)
  • Spool space
  • Procedures