High Skewfactor inspite of even distribution

Database
Enthusiast

High Skewfactor inspite of even distribution

Hi All,

Today i came across a weird scenario in Teradata. 

One of my tables dbc.table_a  was highly skewed with a skewfactor of 95.77 . I used the below query to check the skewfactor.

SELECT

TABLENAME,

SUM(CURRENTPERM) /(1024*1024) AS CURRENTPERM,

(100 - (AVG(CURRENTPERM)/MAX(CURRENTPERM)*100)) AS SKEWFACTOR

FROM

DBC.TABLESIZE

WHERE DATABASENAME= <DATABASENAME>

AND

TABLENAME =<TABLENAME>

GROUP BY 1;

I changed the primary index of the table and the distribution was quite even 23 amps.. 69367 rows. least count on amp was 2894 and highest count of rows on amp was 3170

When I ran the same query again to check the skew factor it was still 95.77. 

But when I changed the tablename to DBC.xys the skewfactor reduced to 5.66.

This happend because even when I dropped table dbc.table_A there were rows in my dbc.tablesize for that table and hence the wrong skewfactor. Any changes that I need to make to the skewfactor query. 

any suggestions??

Tags (1)
6 REPLIES
Enthusiast

Re: High Skewfactor inspite of even distribution

So after you changed the PI and re-loaded the table, are you sure that you checked the skew on the new table (the one with the fixed PI)? 

Senior Apprentice

Re: High Skewfactor inspite of even distribution

Is the tablename longer than 30 characters?

You're using dbc.TableSize instead of dbc.TableSizeV.

Enthusiast

Re: High Skewfactor inspite of even distribution

Yes Dieter.

That solved my problem. How are dbc.tablesize and dbc.tablesizev different?? Will have to find this out.

Thanks a lot!

Senior Apprentice

Re: High Skewfactor inspite of even distribution

Since TD12 all dbc-views without V/VX were legacy because they return CHAR(30) LATIN instead of the new VARCHAR(128) UNICODE, i.e. compatibilty views.

TD14.10 finally enabled long object names, so the old ones were deprecated.

There was enough time to get used to :)

Enthusiast

Re: High Skewfactor inspite of even distribution

Thanks for that piece of information. 

Thanks Dieter!

Enthusiast

Re: High Skewfactor inspite of even distribution

Hi all,

Does Skew factor affect the backup time?