Composite index Vs join index

Database
Enthusiast

Composite index Vs join index

What is the difference between Composite index and join index

Is it this difference that composite index creates sub tables to store pointers to data but Join index re-distribute the entire data

Which is better to use between two

3 REPLIES
Enthusiast

Re: Composite index Vs join index

This is composite index:

CREATE TABLE table1 (

       x1 INTEGER,

       y1 INTEGER,

       z1 INTEGER)

     PRIMARY INDEX (x1, y1);

Teradata Database must update index subtables each time an indexed column value in the

base table is updated or deleted, or whenever a new row is inserted.

This is only a consideration for secondary, JOIN, and hash indexes in the Teradata Database

environment. The more secondary, join, and hash indexes you have defined for a table, the

larger the potential update maintenance downside becomes. 

You can read the material.

Enthusiast

Re: Composite index Vs join index

Raja,

I am not talking about composite primary index but composite secondary index

If you have heard of covered query,it is the query where all the columns of composite index are used in the select statement

My question was about that.......Whether the covered query of the secondary index is better or the join index

Enthusiast

Re: Composite index Vs join index

Can you give me the queries  for both of them and some test data, I will test and let you know.

Partially covered?

I think that JI is better than Partially covered one. However, to prove we have to see many cases and run explain with data:

Maybe you can see this link: