Any relation between joins and indexes

Database
Fan

Any relation between joins and indexes

Hi .. Can someone pls explain how PI & UPI affect

(1) inner join

(2) left outer join

Thanks

3 REPLIES

Re: Any relation between joins and indexes

inner join - join will be faster if join column is a PI (NUPI or UPI ) in both joining tables. A redistribution of joining rows may take place if join colunmn si not a PI in any one table.

outer join - Above case is applicable to outer join as well. Apart from that, join processing may take time if there are multiple non matching rows in right table.

- Nishchint

Fan

Re: Any relation between joins and indexes

Thanks a lot.

Can you also give links to any documentation available on this pls?

Enthusiast

Re: Any relation between joins and indexes

If we are using UPI or NUPI columns from both tables in joining condition then join will happen in AMP's FSG cache (local AMP join) as values of both the columns stored in same AMP (hash value will be same for both table's UPI/NUPI columns so as AMP) hence it is faster.

If col1 of table A is UPI/NUPI and col2 of tableB is not then tableB data will be moved to spool space or if it is smaller then table A then copied to all the AMPs then join will happen hence slower.