Data Skew

SELECT

trim(DatabaseName) AS DatabaseName,

trim(TableName) AS TableName,

max(CurrentPerm)*1.00/min(CurrentPerm) AS Vproc_Skew,

sum(CurrentPerm) AS AllVproc_Perm,

max(CurrentPerm) AS MaxPerm_PerVproc,

min(CurrentPerm) AS MinPerm_PerVproc

/*

sum(CASE WHEN Vproc=00 THEN CurrentPerm ELSE 0 END) AS Vproc00_CurrentPerm,

sum(CASE WHEN Vproc=01 THEN CurrentPerm ELSE 0 END) AS Vproc01_CurrentPerm,

sum(CASE WHEN Vproc=02 THEN CurrentPerm ELSE 0 END) AS Vproc02_CurrentPerm,

sum(CASE WHEN Vproc=03 THEN CurrentPerm ELSE 0 END) AS Vproc03_CurrentPerm,

sum(CASE WHEN Vproc=04 THEN CurrentPerm ELSE 0 END) AS Vproc04_CurrentPerm,

sum(CASE WHEN Vproc=05 THEN CurrentPerm ELSE 0 END) AS Vproc05_CurrentPerm,

sum(CASE WHEN Vproc=06 THEN CurrentPerm ELSE 0 END) AS Vproc06_CurrentPerm,

sum(CASE WHEN Vproc=07 THEN CurrentPerm ELSE 0 END) AS Vproc07_CurrentPerm,

sum(CASE WHEN Vproc=08 THEN CurrentPerm ELSE 0 END) AS Vproc08_CurrentPerm,

sum(CASE WHEN Vproc=09 THEN CurrentPerm ELSE 0 END) AS Vproc09_CurrentPerm,

sum(CASE WHEN Vproc=10 THEN CurrentPerm ELSE 0 END) AS Vproc10_CurrentPerm,

sum(CASE WHEN Vproc=11 THEN CurrentPerm ELSE 0 END) AS Vproc11_CurrentPerm,

sum(CASE WHEN Vproc=12 THEN CurrentPerm ELSE 0 END) AS Vproc12_CurrentPerm,

sum(CASE WHEN Vproc=13 THEN CurrentPerm ELSE 0 END) AS Vproc13_CurrentPerm,

sum(CASE WHEN Vproc=14 THEN CurrentPerm ELSE 0 END) AS Vproc14_CurrentPerm,

sum(CASE WHEN Vproc=15 THEN CurrentPerm ELSE 0 END) AS Vproc15_CurrentPerm,

sum(CASE WHEN Vproc=16 THEN CurrentPerm ELSE 0 END) AS Vproc16_CurrentPerm,

sum(CASE WHEN Vproc=17 THEN CurrentPerm ELSE 0 END) AS Vproc17_CurrentPerm,

sum(CASE WHEN Vproc=18 THEN CurrentPerm ELSE 0 END) AS Vproc18_CurrentPerm,

sum(CASE WHEN Vproc=19 THEN CurrentPerm ELSE 0 END) AS Vproc19_CurrentPerm

*/

FROM DBC.TableSize

WHERE DatabaseName in ('etlrun', 'dwhrun')

HAVING AllVproc_Perm > 100000000 AND Vproc_Skew >=1.25

Group by DatabaseName, TableName

Order by DatabaseName, TableName, Vproc_Skew

;

Tweet