Friday, July 8, 2011

What is a join index ? What are benefits of using Join index?


It is a index that is maintained in a system .It maintains rows  joined on two or more tables. Join index is useful for queries where the index structure contains all the columns referenced by one or more joins, thereby allowing the index to cover all or part of the query

Benefits if using join index is
- to eliminate base table access
- Aggregate processing is eliminated by creating aggregate join index
- It reduces redistribution of data since data is materialized by JI.
- Reduces complex join conditions by using covering queries

1 comment:

  1. Cover queries use the Index subtable to fetch the data. So, if the columns used are included in the JI, they will be stored in the Index subtable. Querying the Index subtable is generally faster than querying the base table.