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
SELECT HASHAMP(HASHBUCKET(HASHROW(var4))) AS AMPNumber, COUNT(*) AS NumberOfRows
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?
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)
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.
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,