Finding and Fixing Skewed Tables Using Viewpoint - Teradata Database

Teradata Database Administration
Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
Product Category
Software

The DBC.TableSizeV contains information that can help identify skewed tables. Table skew results from an inappropriate primary index. A query that references a skewed table may try to process more rows on some AMPs than others, and may run out of space:

  • SELECT statements may run out of spool space if the accessed object has a defined spool space limit.
  • INSERT and UPDATE statements may run out of perm space.

    • 1 Use the following SQL statement to find skewed tables in the DBC.TableSizeV: 

    SELECT vproc AS
    "AMP", TableName (FORMAT 'X(20)'), CurrentPerm
    FROM DBC.TableSizeV
    WHERE DatabaseName = 'database'
    ORDER BY TableName, "AMP" ;

     

     

    In this example, the answer set displays space allocations by AMP for two tables. The results show that:

  • CurrentPerm is similar across all vprocs for employee_upi_onempid. Permanent space distribution is relatively even across all AMPs in the system.
  • The table Employee_nupi_ondept is poorly distributed. The CurrentPerm figures range from 4,096 bytes to 30,208 bytes on different AMPs.

    • 2 Redefine the primary index for any skewed tables that you find. See “Choosing a Primary Index” on page 63.