All Forums Database
khatriprad 12 posts Joined 12/10
16 Sep 2014
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

Pradeep
Raja_KT 1246 posts Joined 07/09
16 Sep 2014

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.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

khatriprad 12 posts Joined 12/10
16 Sep 2014

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

Pradeep

Raja_KT 1246 posts Joined 07/09
17 Sep 2014

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:

 

http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/Database_Management/B035_1094_111A/ch11.052.053.html

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.