Join Indexes v/s classic Denormalized base table

Not applicable

Join Indexes v/s classic Denormalized base table


Join Indexes ends up creating a denormalized base table (inaccessible to users) with appropriate PI.

Trying to look into the advantages of creating a Join Index over classic denormalized table (normally discouraged by Teradata)

1) Join Index behaves like base table with PI doing data distribution and hence data access is fast. Because of this it be better than a View on same table/column as the data access in view will be dependent on PI and SI of the base tables which may or maynot appropriately cover the query in question
2) We don't have to write script to populate the denormalized base table

Any key things I am missing here ? What are the disadvantages ?

Re: Join Indexes v/s classic Denormalized base table

The changes are real time (not so in denormalized tables).

Single Table JIs with different PIs are quite helpful in speeding up joins ..

Sp**** Indexes possible for a subset of data ....

You can't use FL/ML on JIs ...

Updates to parent table are effected due to Index maintenance (like any other SI, may be a bit more )

those are some of the things I could quickly think of ..