Multicolumn primary index in joins

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Multicolumn primary index in joins

Hi All,

 

I just have a question on Multicolumn primary index usage in joins.

I have created multi column PI on Employee table and single column PI on Employee_phone table.

 

CREATE TABLE EMPLOYEE ( ENO SMALLINT, ENAME VARCHAR(10), LOCATION VARCHAR(10),DNO SMALLINT) PRIMARY INDEX(ENO,ENAME);


CREATE TABLE EMPLOYEE_PHONE ( ENO SMALLINT, PHONE SMALLINT) PRIMARY INDEX(ENO);

SEL * FROM EMPLOYEE EMP
INNER JOIN EMPLOYEE_PHONE EP
ON EMP.ENO=EP.ENO

 

In the above join, we are joining EMP.ENO (part of PI) and EP.ENO (PI). Then how does join processing happens, does this join has the PI advantange? 

 

Thanks in advance


Accepted Solutions
Highlighted
Senior Apprentice

Re: Multicolumn primary index in joins

(Sorry, I missed the other part of your question - " how does join processing happens?")

 

The join strategy used will usually vary based on data volume:

- very small tables: one of them will be copied into spool and they will be joined using a hash join or product join

- with large tables (unless the Employee table is big and a LOT bigger than the Emp_Phone table): the Employee table will probably be redistributed and sorted on the hash code of the EMP column (the join column) and then you'll get a merge join between the spool file and the Emp_Phone table.

 

Yes, there could be other variations but I think you'll find that those are the most likely.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
3 REPLIES
Senior Apprentice

Re: Multicolumn primary index in joins

Hi,

No, this join will not use a 'PI:PI' join.

To get that type of join you essentially have to:

- join on all columns of the PI in both tables

- all joins conditions must be equality

- all join conditions must be AND'd together

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Highlighted
Senior Apprentice

Re: Multicolumn primary index in joins

(Sorry, I missed the other part of your question - " how does join processing happens?")

 

The join strategy used will usually vary based on data volume:

- very small tables: one of them will be copied into spool and they will be joined using a hash join or product join

- with large tables (unless the Employee table is big and a LOT bigger than the Emp_Phone table): the Employee table will probably be redistributed and sorted on the hash code of the EMP column (the join column) and then you'll get a merge join between the spool file and the Emp_Phone table.

 

Yes, there could be other variations but I think you'll find that those are the most likely.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Multicolumn primary index in joins

Thank you Dave.. that answers my question..