15.00 - Explicit Procedure - Teradata Database

Teradata Database Design

Teradata Database
User Guide

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 DBC.TablesizeV for the space occupied by the new base table.

“Querying DBC.TableSizeV” on page 873 provides a representative SQL query for you to use for this purpose.

4 Record the number of bytes returned by the query.

5 Add a secondary index to the table.

6 Query the system view DBC.TablesizeV 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.