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.
what is semantic model and layer in teradata and in what cases we can implement them in real cases.
ReplyDeleteSemantic model /layer is an functional layer applied over ETL data.
DeleteFor example ,
if you have loaded data into ETL (after all transformations) , then we build semantic layer (or datamarts which are specific to client's requirements)