Archives of the TeradataForum

Message Posted: Wed, 27 Jun 2012 @ 10:07:09 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

This is worst case.

Updating the PI of a table is like updating a Clustered Index in other DBMSes:

Delete the row on one AMP (and maintain all secondary indexes), then re-insert it on another AMP (and maintain all secondary indexes).

One of the basic rules for choosing the PI of a table (or a ClusteredIndex) is "volatility", it should be a column which is hardly modified.

You usually write an update of the PI columns for a large number of rows once and then you'll always remember that horrible speed :-)

For a SET table each inserted/updated row must be compared to all other rows with the same RowHash if it's exactly the same. If you have hundreds of rows per PI-value there's a huge CPU overhead for those Duplicate Row Checks.

You should check DBQL which step uses most ressources.

If it's a recurring update you should consider switching to MULTISET and/or changing the PI.

If it's a one-time update for a large number of rows Insert/Select into a new multiset table might be more efficient.

Dieter

	<Prev	Next>		<<First	<Prev	Next>	Last>>