All Forums Database
debu_ju 1 post Joined 05/13
28 Aug 2013
Big table - very big table join

Hi All,
I have a performance issue with a query. Below is the sample query where sales table does not have any index on cust_ky, but rep has cust_ky as a NUPI. Volume of rep is about 13 million and sales is 100 million.
 
select r.rep_nm, s.qty
from sales s join rep r on s.cust_ky = r.cust_ky
 
1. What should be the ideal join strategy for this case? 
2. Is a NUSI on sales.cust_ky going to help? 
 
Thanks
Debu

dnoeth 4628 posts Joined 11/04
29 Aug 2013

Hi Debu,
#1: if there are statistics on the join columns you should trust the optimizer to pick the best plan
#2: no, Teradata rarely uses SIs for joins (unless it's a very small subset of the data)
 
Dieter

Dieter

graebige 5 posts Joined 10/11
30 Aug 2013

is there a PPI on available that you could use, I only see 2 full table scans - also, reduce the data amount by using derived tables

dhirajpalse 36 posts Joined 01/08
03 Sep 2013

This would go for HASH join where smaller table (REP) is duplicated on all AMPs based on hashing CUST_KEY.
If it is a frequently used query, then creating hash index on REP table is a good option.
This would enhance the select query performance, but would consume more space and may cause longer DML operations on REP table.

Dhiraj

You must sign in to leave a comment.