LOBS(XML) not distributed evenly across amps

Database
Teradata Employee

LOBS(XML) not distributed evenly across amps

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.

Capture.PNG

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 

Tags (3)

Accepted Solutions
Highlighted
Teradata Employee

Re: LOBS(XML) not distributed evenly across amps

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
Teradata Employee

Re: LOBS(XML) not distributed evenly across amps

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.

Teradata Employee

Re: LOBS(XML) not distributed evenly across amps

Thank you for your answer.