Database

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 for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

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

03-17-2017
10:10 AM

03-17-2017
10:10 AM

Hi all,

I have a set of xml files that is equal to the number of amps of the Teradata system.

My first column (ID) is the unique primary index, and the second column is the CLOB (XML). Normally I would expect the rows to be distributed evenly across amps; however, this is currently not the case. On some amps 3 or 4 of the CLOBS are placed on other amps none of the CLOBS are placed. This is a problem because the xml SP/functions create I/O skew, and CPU skew.

I would guess that distributing LOBS works a bit differently because of the subtables, but when the LOBs are used they will be put together on that amp, and thus create skew. Is there a way to force an even distribution of LOBs?

Kind regards,

Piet

Solved! Go to Solution.

Accepted Solutions

Highlighted

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

03-17-2017
10:25 AM

03-17-2017
10:25 AM

Distribution works exactly the same. The hash of the PI value is used to distribute the rows. If the same PI values were inserted into a table without LOBs, the distribution would be the same. The hashing mechanism results in statistical evenness of the distribution over a large number of PI values. For a small number, it in no way guarantees exactly even distribution. If many rows are to be inserted, then the distribution will even out.

If it is only ever intended to have this small number of rows, some experimentation with PI values using the hashrow, hashbucket, hashamp functions would allow selection of PI values to force even distrbution.

1 ACCEPTED SOLUTION

2 REPLIES

Highlighted

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

03-17-2017
10:25 AM

03-17-2017
10:25 AM

Distribution works exactly the same. The hash of the PI value is used to distribute the rows. If the same PI values were inserted into a table without LOBs, the distribution would be the same. The hashing mechanism results in statistical evenness of the distribution over a large number of PI values. For a small number, it in no way guarantees exactly even distribution. If many rows are to be inserted, then the distribution will even out.

If it is only ever intended to have this small number of rows, some experimentation with PI values using the hashrow, hashbucket, hashamp functions would allow selection of PI values to force even distrbution.

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

03-27-2017
02:09 AM

03-27-2017
02:09 AM

Thank you for your answer.

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.