Below is my understanding of Join Indexes. Please help me correct if i am missing somewhere.
Join Index creates a seperate storage in the PERM Space, used to reduce the CPU usage time. And the optimizer decides which is the best plan to access the data.
1) Single Table Join Index:
Here we change the Primary index of the base table. Eg. Table A(Primary Key) is Joined with Table B(Candidate Key). When these tables are joined Table B' s data might be duplicated in all the amps. To avoid that we create a single table join index on Table B. By which we avoid duplication of data in all the amps.
2)Multi Table Join Index:
When Table A(Candidate Key) and Table B(Candidate Key) is joined together. Because of which the data from both the tables would be duplicated in all the amps. To avoid that we create a multi table join index. Because of which for every insert,update or delete it would take lot of time(Performance wise).
When a particular TABLE along with a WHERE clause is queried frequently. It would increase the performance by creating a Sparse Index
It is applied on a one or more TABLE(When Joined). Only on columns which has SUM or COUNT aggregate.
(Not aware on what it does internally to improve the performance)
In the Single or Multi table Join Index we use the rowid to refer to the base tables which would return values that do not belong to the actual SELECT QUERY. Whereas when we use the HASH INDEX the data is not sorted internallu and it by default refers to the base table.