Archives of the TeradataForum
Message Posted: Mon, 15 Oct 2001 @ 15:32:02 GMT
| Subj: | | Re: INSERT INTO big table |
| |
| From: | | Paul Johnson |
I have recently used MULTISET to gain a massive performance increase. In fact, our simple insert/select jobs would never finish without
it.
Consider the case where you might have a high number (100,000+?) of instances of a couple of out-of-the-ordinary NUPI values in an
otherwise evenly distributed table. Transaction level data logged against a write-off account within a retail organisation may be an
example.
The file system can have immense difficulty in placing the rows for the small number of PI values for which very many instances exist.
This is due to the high number of 'read aheads' it has to do to ensure that it is not about to create a duplicate row. Changing the table
from a SET (default) to a MULTISET removes the need for the read-aheads and performance can increase dramatically. The issue here is not
distribution, but data 'lumps' within the amp on which the rogue cases exist.
The issue of machine upgrades is quite interesting. Teradata has a much-vaunted ability to scale linearly, as we are no doubt all aware.
That's only useful if you can get hold of the required kit at the time the upgrade is required. This may be years after the original
hardware was installed. Most 'upgrades' that I have seen involve the purchase of a new platform, rather than an extension to the existing
platform, leaving the customer with an ever increasing number of NCR boxes lying around.
Paul Johnson.
| 
