Monday, December 13, 2010

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.

2 comments:

  1. what is semantic model and layer in teradata and in what cases we can implement them in real cases.

    ReplyDelete
    Replies
    1. Semantic model /layer is an functional layer applied over ETL data.

      For 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)

      Delete