- Create a table with two columns.
- Choose PI as a date column, which is skewed:
CREATE volatile MULTISET TABLE MY_SKEW_TABLE ( ITEM_ID DECIMAL(18,0) NOT NULL, Today_DT DATE NOT NULL)PRIMARY INDEX (Today_DT)on commit preserve rows;
- Insert the first day of data (100,000 rows).
- Check the distribution of your PI; try this query:
/**Hash AMP which row query**/LOCKING ROW FOR ACCESSSELECT HASHAMP(HASHBUCKET(HASHROW(Today_DT))) AS WhichAMP,COUNT(*) AS RowsPerAMP FROM MY_SKEW_DB.MY_SKEW_TABLEGROUP BY 1ORDER BY 2 DESC;
- Sort the data in the following format to check which AMP holds the most number of rows:
WhichAMP |
RowsPerAMP |
120 |
100,000 |
324 |
0 |
12 |
0 |
1 |
0 |
45 |
0 |
239 |
0 |
- Now, to resolve this ...