Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 26 Nov 2001 @ 15:13:46 GMT


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


Subj:   Opinions on choosing the Primary Index
 
From:   Claybourne Barrineau

All,

I have 2 tables that I need to join together on the one column they have in common. When I choose this column (Column A) to be the NUPI for these 2 tables, the query runs as a merge join in about 1.5 hours. Unfortunately, Column A is very unevenly distributed, and I have some amps working much harder (~200%) than other amps.

Using the same SQL, as a test, I changed the primary indexes of these 2 tables in a way which "encouraged" the Optimizer to perform an AMP Local Hash Join of the resulting partitions of the larger table to the duplicated smaller table. This query ran about 20% faster, and the query was nearly perfectly distributed across the amps. Total CPU Seconds were slightly higher for this new query.

Pros of altering the PIs:

- Even distribution of each Amps Spool and CPU usage (...resulting in greater overall throughput for the box)

- 20% faster

Cons of altering the PIs:

- Illogical PIs

- Current method of "Encouragment" for the Optimizer might not work in future releases of the RDBMS

- Slighly Higher CPU Seconds

Does anyone have strong Opionons regarding this type of "encouragement" for the optimizer?


Thanks,

Claybourne Barrineau



     
  <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: 15 Jun 2023