What is the difference between Secondary Index and Join index?

General

What is the difference between Secondary Index and Join index?

 
1 REPLY
Enthusiast

Re: What is the difference between Secondary Index and Join index?

Hariharan, Conceptually both of them are in no way comparable.

Secondary index provides an alternate path to reach the base data in a table. There is a subtable built on top of the actual table which may or may not reside on the same AMP. This is specific to a table but not spread across.

Join Index can be created on a single or multiple tables and is called in multiple ways. This is like a materialized view in which the whole data selected as part of the join is created across the database and any query using the Join index need not hit the base table but can pull data from the Join Index itself. This is however not accessible to the user.

Space wise both have a overhead but JI in most cases would be having more overhead than SI.