Database

turn on suggestions

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

Showing results for

- 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.

Labels:

Accepted Solutions

- 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

- 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

Re: LOBS(XML) not distributed evenly across amps

Thank you for your answer.