Handling Teradata SKEW with RANDOM function

Data skew in Teradata occurs when data is unevenly distributed across the AMPs (Access Module Processors), leading to performance issues, particularly during joins or aggregations. The RANDOM() function can be employed as a salting technique to mitigate this skew.

Using RANDOM() to Handle Skew:

• Identify the Skewed Column: 

  Determine the column in a table that exhibits significant data skew, meaning a few values occur much more frequently than others.
• Generate Random Values: 

  Create a new column or a temporary column populated with random numbers using the RANDOM() function. The range of random numbers should be chosen based on the desired level of distribution. For example, RANDOM(1, 100) generates random integers between 1 and 100.
• Combine with Skewed Column: 

  Concatenate the skewed column with the newly generated random value. This creates a composite key.

    SELECT 

        PrimaryKeyColumn,

        SkewedColumn,

        TRIM(CAST(RANDOM(1,100) AS VARCHAR(3))) AS RandomSalt,

        SkewedColumn || TRIM(CAST(RANDOM(1,100) AS VARCHAR(3))) AS SaltedJoinColumn

    FROM 

        YourSkewedTable;

• Perform Join/Aggregation on Composite Key: When performing joins or aggregations, use this composite key instead of the original skewed column. This distributes the frequently occurring values across different AMPs, as the random component ensures unique hash values for each instance of the skewed value.

    CREATE VOLATILE TABLE NotSkewingTable AS

    (

        SELECT 

            PrimaryKey, 

            SkewedColumn,

            SkewedColumn || TRIM(CAST(RANDOM(1,100) AS VARCHAR(3))) AS SaltedJoinColumn

        FROM 

            YourSkewedTable

    ) WITH DATA

    ON COMMIT PRESERVE ROWS;

    SELECT 

        T1.ColumnA, 

        T2.ColumnB

    FROM 

        AnotherTable T1

    JOIN 

        NotSkewingTable T2

    ON 

        T1.JoinColumn = T2.SaltedJoinColumn;

Benefits:

• Improved Parallelism: Distributes the workload more evenly across AMPs, leading to faster query execution.
• Reduced Spool Space: Minimizes the need for large intermediate spool files, which can consume significant resources.
• Enhanced Join Performance: Addresses the bottleneck caused by skewed join keys.

Considerations:

• Overhead: 

  Generating and concatenating random values adds a small overhead, but the performance gains typically outweigh this.
• Cardinality: 

  The range of random numbers should be sufficient to effectively distribute the skewed data.
• Data Types: 

  Ensure compatible data types when concatenating the skewed column and the random number.
• Temporary Nature: 

  This technique is often applied in temporary or volatile tables for specific operations to avoid altering the base table structure.