Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Mon, 15 Oct 2001 @ 15:32:02 GMT

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

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.

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020