Columnar - Get List of Columnar tables - Fix Skewness

Database
Teradata Employee

Columnar - Get List of Columnar tables - Fix Skewness

How can we get list of columnar tables. As per my knowledge Columnar are NoPI tables, so in case we found skewness for them, how can we fix their skewness then?

Tags (3)

Accepted Solutions
Senior Apprentice

Re: Columnar - Get List of Columnar tables - Fix Skewness

Hi,

 

NoPI tables are identified by DBC.TablesV.Tablekind = 'O'.

 

However, since TD 15.10, columnar tables can have either a Primary AMP index or a regular PI.

 

To identify CP tables then try this:

SEL *
FROM dbc.partitioningconstraintsv
WHERE columnpartitioninglevel > 0;

 

If you're on TD 15.10 or higher then specifying a PA or PI for those tables will help fix skew - just like a PI does for regular tables.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
4 REPLIES
Senior Apprentice

Re: Columnar - Get List of Columnar tables - Fix Skewness

Hi,

 

NoPI tables are identified by DBC.TablesV.Tablekind = 'O'.

 

However, since TD 15.10, columnar tables can have either a Primary AMP index or a regular PI.

 

To identify CP tables then try this:

SEL *
FROM dbc.partitioningconstraintsv
WHERE columnpartitioninglevel > 0;

 

If you're on TD 15.10 or higher then specifying a PA or PI for those tables will help fix skew - just like a PI does for regular tables.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Columnar - Get List of Columnar tables - Fix Skewness

Thanks a lot Dave.

 

We have TD15.10 and the only Columnar table on our system is a NoPI. Its not highly skewed at the moment.

But, what do u suggest if it gets skewed, should we fix it like we fix any NoPI table (re-inserting data in NoPI table using HASH BY RANDOM)?

Tags (3)
Senior Apprentice

Re: Columnar - Get List of Columnar tables - Fix Skewness

Hi Mobeen,

 

You could try that or use my suggestion from above "If you're on TD 15.10 or higher then specifying a PA or PI for those tables will help fix skew - just like a PI does for regular tables."

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Columnar - Get List of Columnar tables - Fix Skewness

Thanks a lot for all the information Dave.