How do I improve performace of a query which has non PI joins ? There are some tables which are getting joined on non PI columns and hence, lot of redistribution is happening , hence leading to high spool usage while processing . Will single table join index help in this scenario ?
Yes, it will help. If you choose the columns which usually get redistribution when the table is joined you will save that redistribution. That distribution will be done when you execute insert on the table.