All Forums Database
07 May 2014
Calculation Skew

Calculation Skew
I have some problems calculatine Skew base don column combinations. I found something like this:

SELECT 100-((AVG(s.NumberOfRows)*100)/MAX(s.NumberofRows)) AS SkewFactor 
  FROM (
        SELECT HASHAMP(HASHBUCKET(HASHROW(var4))) AS AMPNumber, COUNT(*) AS NumberOfRows 
          FROM T0 
         GROUP BY 1 ) AS s

But the result do not match the values I get if I right click the table in Teradata Administrator and Select SpaceSumary The SkewFactor I find here are not the same as the one calculated in the Select statement.
I have only primary index= var4 in the table T0, therefore I expected to get the same SkewFactor from my SQL and from the TeradataAdministrator.
Can anybody help me with a SQL the calculated the SkewFactor in the same way as Teradata Assistant?
Peter Schwennesen

Tags:
dnoeth 4628 posts Joined 11/04
07 May 2014

Hi Peter,
when you press F12 in TD Admin you can see the submitted query in the "SQL History". You'll notice that the SQL is using a similar calculation, but it's not based on the actual rowcount, it's based on perm space, i.e. dbc.TablesSizeV.
Using the table size is much more effcient than doing a Full Table Scan to get the actual rowcount and you can get the skew for multiple tables in a single query ("Table Space" when you right click on the database name)

Dieter

07 May 2014

Hi Dieter
Thanks for the F12 information, it may be useful! I did not know about the F12.
My Skew calculation did not match because it did not consider the amps where there is no row, I have added dummy amps with no row counts, and not it nearly get the same calculation as in the Administrator.
Peter

LUCAS 56 posts Joined 06/09
16 May 2014

Hi,
I send again my previous question about TD ADMIN (14.10) not displaying some tables
( "Table Space" option):
some empty tables are not displayed , even small tables (2 lines) are not displayed !
This is very confusing, especially when a SQLA Request doesn't find a lot of tables.
When copying the SQL from the "F12 window list" (updated with the table name ) the answer is OK ...
Thanks for any comment,
Pierre

You must sign in to leave a comment.