Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 07 Feb 2008 @ 09:27:52 GMT


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


Subj:   Re: Dealing with concentrated values in the data
 
From:   Dieter Noeth

Anomy.Anom wrote:

  We have some tables that have concentrated values within the NUPI.  


  CASE 1: 10M row table; 1M rows with modal value Normal_Tuesday  


  CASE 2: 10M row table; 100K rows with modal value FAT_Tuesday  


  CASE 3: 10M row table; 10K rows with modal value Super_Tuesday  


  CASE 4; 10M row table; 1k have the with modal value Tuesday  


  Row lengths are 125 bytes, blocking is at 128K, so ~1000 rows per block.  


  What is the performance impact on loads in the cases?  


Basic condition: table must be MULTISET.

Forget about case 4.

Case 1 & 2 (& 3): There will be hardly any impact for inserts, but deletes and updates will suffer, if restricted using the PK or just a few rows, because the file system has to locate the matching rows searching all the datablocks.


  What is the performance impact on queries that target the modal value?  


Fast, if you really request all the rows, but the same as Updates/Deletes above if just a few rows.


  What is the performance hit on table scans with residual conditions involving joins on the PI to another table with  


  a) same PI  


  b) different PI  


I'd suggest to run some tests, might be slower, especially if the table is heavily skewed.


Dieter



     
  <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: 23 Jun 2019