It is type of join index which contains a WHERE clause , that reduces number of rows participating from the base tables.
All types of join indexes, including single table, multitable, simple or aggregate can be sparse.
By default , a given join index will have a NUPI defined on the first column if it is not specified in create JOIN index statement. We can explicitly mention a column to be NUPI for the join Index.
Any combination of AND, OR, IN (LOGICAL) conditions can be used along with WHERE clause in Sparse Index.
Check the following example for Sparse Join Index.
CREATE JOIN INDEX DEV.testjoin AS
sel A.DEPTno , b.sal
WHERE a.deptno=20 --- sparse index
PRIMARY INDEX (sal);
In the following DDL for Join INDEX,
- Sal is explicitly mentioned as NUPI
- a filter clause is applied on Dept.DeptNO = 20, which reduces the number of rows ( an Ideal case of Sparse Index )
P.s: Join Index never allows a Unique Index to be created.