Join Indexes

Join Indexes

The join index JOIN the two tables together and keeps the result set in the permanent space of TD.

The join index will hold the result set of 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-

1.       Multi table Join Index- Suppose we have two BASE tables Employee and Dept, 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 empno, empname, emp_dept, emp_sal, emp_mgr

From employee e inner join dept d

On e.emp_dept=d.deptno

Unique primary index (empno);

This way the JOIN index EMP_DEPT holds the result set of two BASE tables and at the time of JOIN, PE will decide whether 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.

2.       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 empno, empname, emp_dept

From employee

primary index (empdept);

3.       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

Empno, sum(emp_sal)

From emp_salary

Group by 1;

The main fundamentals of JOIN indexes are:

1.       JOIN index is not a pointer to data it actually store data in PERM space.

2.       Users never query them directly, its PE who decide which result set to take.

3.       Updated when base tables are changed.

4.       Can’t be loaded with fastload or multiload.