Calculation Skew

Database
Enthusiast

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 (1)
3 REPLIES
Junior Contributor

Re: Calculation Skew

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)

Enthusiast

Re: Calculation Skew

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

Enthusiast

Re: Calculation Skew

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