Sparse Index ( SPARSE JOIN INDEX)

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

FROM 

dept  A

INNER JOIN

 employee B

ON  A.deptno=b.dept

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.