«

»

Dec 25

Join Indexes



The join index JOIN the two tables together and keeps the result set in the permanent space of Teradata. This JOIN index will hold the result set of the two table, and at the time of JOIN parsing engine will decide whether it is fast to build the result set from the actual BASE tables or the JOIN index. User never directly query the JOIN index. In the sense JOIN index is the result of joining two tables together so that parsing engine always decide to take the result set from this JOIN index instead of going and doing manual join on the base table.

Types of JOIN index –
Multi table JOIN index

Suppose we have two BASE tables EMPLOYEE_TABLE and DEP_TABLE, which holds the data of EMPLOYEE and DEPARTMENT respectively. Now a JOIN index on these two tables will be somewhat-

CREATE JOIN INDEX EMP_DEPT
AS
SELECT EMP_NO,EMP_NAME, EMP_DEPT, EMP_SAL, EMP_MGR
FROM EMPLOYEE_TABLE EMP
INNER JOIN DEP_TABLE DEP
ON EMP.EMP_DEPT = DEP.DEPT_NO
UNIQUE PRIMARY INDEX (EMP_NO);
 

This way the JOIN index EMP_DEPT holds the result set of two BASE tables, and at the time of JOIN PE will decide weather it is faster to join actual tables or to take result set from this JOIN index. So always choose wise list of columns and tables to create JOIN index.

Single Table JOIN index

A single table JOIN index duplicate a single table, but changes the primary index. Users will only query the base table and its PE who decide which result set is faster, from JOIN index or from actual BASE tables. The reason to create the single table JOIN index is so joins can be performed faster because no redistribution or duplication needs to occur.

CREATE JOIN INDEX EMP_SNAP
AS
SELECT EMP_NO, EMP_NAME, EMO_DEPT
FROM EMPLOYEE_TABLE
PRIMARY INDEX(EMP_DEPT);

Aggregate JOIN index

An aggregate JOIN index will allow the tracking of Averages SUM and COUNT on any table. This JOIN index is basically used if we need to perform any aggregate function in the data of the table.

CREATE JOIN INDEX AGG_TABLE
SEL
DEPT,
AVG(EMP_SAL)
FROM EMP_SALARY
GROUP BY 1;

The main fundamentals of JOIN indexes are

    • JOIN index is not a pointer to data it actually store data in PERM space
    • Users never query them directly, its PE who decide which result set to take
    • Updated when base tables are changed
    • Can’t be loaded with Fastload or Multiload.

 


54 pings

Skip to comment form

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.