Join Index

Join index

• The Join Index joins the two tables together and keeps the result set inside PERM Space.
• Users don’t ever query the Join Index, but only query the base tables.
• If a user does join these two tables together the Parsing Engine will be the one to decide if it is faster to build the answer set from the Join Index instead of actually performing the Join on the Base Tables.
• In a sense the Join Index is the result of joining the two tables together so the Parsing Engine will almost always decide to take the data from the Join Index instead of joining the tables again manually.
• When the base tables are updated so is the Join Index, thus keeping everything in sync.
• In Oracle these are called Materialized Views.
• It's not Pointers, but actual data is stored.
• can have Non_Unique and Non_secondary indexes. collect statistics on PI ans SI.

Multi - Table Join index:

• A Multi - table join index is a join index that involves two or more tables.

Ex:- CREATE JOIN INDEX INDEXNAME AS 

SELECT COL1.T1,COL2.T1,

COL1.T2,COL2.T2 

FROM T1 INNER JOIN T2

ON COL1.T1= COL3.T2 

PRIMARY INDEX(COL1.T1);

Single Table Join Index:-

• A Single-Table Join Index duplicates a single table, but changes the Primary Index.
• Users will only query the base table, but the Parsing Engine will use the Join Index if it is deemed to be faster.
• The reason to create a Single-Table Join Index is so joins can be performed faster because no Redistributions or Duplication needs to occur.

Ex:- CREATE JOIN INDEX INDEXNAME 

AS 

SELECT COL1,COL2,COL3 

FROM T1 PRIMARY INDEX(COL2);

• we have Just duplicated the T1 able, but with a diffrent primary index.(In base table we have PI col1)
• This usually done for join processing. when we join the table with another table, No data will redistributed or duplicate.

Aggregate Join Index:-

• An Aggregate Join Index will allow tracking of the Aggregates SUM and COUNT on any table.
• This is used in conjunction with the Customer_Number and the Extraction of Month and Year from the Order_Date.
• As the base table changes so does the values in the Join Index to reflect the changes.

Ex: CREATE JOIN INDEX INDEXNAME AS

SELECT OL1,COL2,COL3

EXTRACT(YEAR FROM COL4) AS YR,

COUNT(*),

SUM(COL5)

FROM T1 GROUP BY COL1,COL2,COL3;

• Only the aggregates of sum and count can be used with an aggregate join index.
• Extract month, year are allowed.
• join index is update as base table changes.
• Count and sum required to be data type float.