Sparse join index

SPARSE join index is being created in Teradata as a result of filtering the rows while joining with the multiple tables.
They are a type of Join Index which contains a WHERE clause that reduces the number of rows which would otherwise be included in the index. 
All types of join indexes, including single table, multitable, simple or aggregate can be sparse. 

Example code:

CREATE JOIN INDEX ind_employee AS
SELECT  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);

Note:
Join Index never allows a Unique Index to be created.

Explanation:
--> 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 )

how To find join indexes that are defined on your table

1. SELECT * FROM dbc.indices
2. WHERE indextype='J'
3. AND databasename='your_database_name'
4. AND tablename='your_table_name'
5. ORDER BY indexname,columnposition;

How to find out list of indexes in Teradata?

How to find out list of indexes in Teradata?

IndexType	Description
P	Nonpartitioned Primary
Q	Partitioned Primary
S	Secondary
J	join index
N	hash index
K	primary key
U	unique constraint
V	value ordered secondary
H	hash ordered ALL covering secondary
O	valued ordered ALL covering secondary
I	ordering column of a composite secondary index
M	Multi column statistics
D	Derived column partition statistics
1	field1 column of a join or hash index
2	field2 column of a join or hash index

SELECT

databasename, tablename, columnname, indextype, indexnumber, indexname

FROM  

dbc.indices

ORDER BY

databasename,  tablename, indexnumber;

How does indexing improve query performance?

Answers:

Indexing is a way to physically reorganize the records to enable some frequently used queries to run faster.

The index can be used as a pointer to the large table. It helps to locate the required row quickly and then return it back to the user.

Or

The frequently used queries need not hit a large table for data. They can get what they want from the index itself. - cover queries.

Index comes with the overhead of maintenance. Teradata maintains its index by itself. Each time an insert/update/delete is done on the table the indexes will also need to be updated and maintained.

Indexes cannot be accessed directly by users. Only the optimizer has access to the index.