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.
- 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" ; AMP TableName CurrentPerm --- -------------------- ----------- 0 employee_upi_onempid 18,944 1 employee_upi_onempid 18,944 2 employee_upi_onempid 18,944 3 employee_upi_onempid 19,968 0 employee_nupi_ondept 4,096 1 employee_nupi_ondept 30,208 2 employee_nupi_ondept 15,360 3 employee_nupi_ondept 12,288
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.
- Redefine the primary index for any skewed tables that you find.