Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic to the Top
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
Options
# How to reduce skew factor on stage tables with NoPI?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-21-2013 08:10 AM

03-21-2013 08:10 AM

Hi,

I am new to teradata. I have several stage tables defined as NoPI.

I notice most stage talbes have skew factor > 50. Is there a way to reduce this.

What is the best way to handle this situation for stage tables with NoPI?

Any ideas. please advise.

Thanks,

VM.

20 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-21-2013 11:50 AM

03-21-2013 11:50 AM

How large are those tables?

And how are they loaded?

Dieter

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-22-2013 02:46 AM

03-22-2013 02:46 AM

Hi VM,

These are probably small tables tables loaded with batch load TD utility.

Even though it is true that your NoPI tables will be loaded in round robin manner in this case, the round robin is applied on block level (not row level).

This implies that in the case of small tables that fit in one (a couple) of blocks, all the data will end up on a single (a couple) of AMPs. That should explain the high skew factor.

I would recommend distributing those tables on appropriate PI column.

You won't get any benefits on FastLoading a small NoPI table.

Cheers,

Igor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-05-2013 06:10 PM

08-05-2013 06:10 PM

I have same problem.

My Table size 480 GigaByte and skew factor > 78 . It is 7 column table(Using 3 Column as primary Index).

I create also NoPI table but still skew factor is same.

How i reduce it ? Please help me.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-05-2013 11:50 PM

08-05-2013 11:50 PM

If you created the NoPI table from the PI Table the skew will be the same (AMP local). You must create the NoPI table EMPTY and load it from scratch. This should guarantee a good distribution (e.g.: low skew).

HTH.

Cheers.

Carlos.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-07-2013 03:56 AM

08-07-2013 03:56 AM

In TD14 there's also a new option for INSERT/SELECT into NoPI tables, HASH BY RANDOM, to reduce skew.

Dieter

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-18-2014 07:35 AM

03-18-2014 07:35 AM

Hi,

I have a table in PRON which is having 3 Billion data. There are 4 Primary Index column, Partition on Date and secondary Index. when I checked the Distint Primary Indix on the combination of 4 columns. it is aboly 2.98 Billion. Percentage of duplicates is about 2%.

But the Skew in the table is 54. Can you plese explain how reduse the Skew factor.

Appriciate your help.

Thanks,

Manju

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-18-2014 02:40 PM

03-18-2014 02:40 PM

Hi Manju,

Secondary index will not impact skew factor.

Skew factor can be reduced by choosing more evenly distributed set of columns. if your 2% is going to one amp then you can find out what distinguishs it in other columns from each other.

But reducing skew factor by choosing more appropriate Primary index column set will not help Teradata performance unless it is used in SQLs. Primary index needs to be mostly usable in sqls as whole and not partially and nearly evenly distributed.

Thanks

Harpreet

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-20-2014 10:12 AM

03-20-2014 10:12 AM

Thanks Harpeet ...

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-16-2014 10:44 AM

04-16-2014 10:44 AM

hi

can we take a **column** which is** declare as** **compress(0)** as PI column.

max how many column we can go for combination to find better pi for skew

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.