Join Index

Database
Enthusiast

Join Index

Is join index create a table?

If yes, and if there is change in any of the joined tables will it reflect in the join index table?
10 REPLIES
Enthusiast

Re: Join Index

Creating a join index results in a JI subtable creation. The concept is similar to how Secondary index creation results in a SI subtable creation. Whenever the data subtable is updated, any updates that should get reflected in the Join index / Secondary index subtable is also applied atomically by the Database.
Enthusiast

Re: Join Index

Join Index creates a separate Table and anychanges done to base table automatically those changes will get reflect in index table .Teradata take care of this.

1.How ever When you load data through Multi load and Fast load if table is having Join Index then it will through error.
Enthusiast

Re: Join Index

hi,

Thanks for the replies.

If we can consider join index like views in oracle or sql server, what makes it fail a multiload ????? Is there any reason which blocks multiload operation on the table which has join index?
Enthusiast

Re: Join Index

The load utilities (multiload and fastload) were simply not designed to maintain join indexes. The load utilities don't support a number of newer features (e.g., triggers, LOBs, RI). For tables that use those features, data can be loaded first into a temporary staging table, then transfered in bulk using standard SQL statements, such as INSERT or MERGE.
Enthusiast

Re: Join Index

Hi Jim,

Is this because load utilities like multiload and fastload read/writes data in block and not in rows?How about tpump, does it support Join Index on target table?
Enthusiast

Re: Join Index

TPUMP is really just a front end for standard SQL operators and does not have any of the limitations of Fastload and Multiload.

Fastload and Multiload have these limitations mainly because a decision was made not to enhance them (for now, anyway) to support certain newer features. In the future ... who knows?
Enthusiast

Re: Join Index

One of the critical working intrinsics of both Multi Load and Fast load are that they are AMP local. That's One good reason for not supporting JI...

But yes you can have a single Table JI with same PI, which would be AMP local, so it doesn't answer the question why mload won't support one of that kind. My guess would be that a single table JI with same PI could be mimicked by an NUSI for most part. (which is supported my mload)

As far as Fastload not supporting anything ... well who would want indexes on an empty table anyway ;-) ... probably instead of internally recreating all the indexes at the end of a load, they just shoved it off to the App dev guys. :o
Enthusiast

Re: Join Index

Hi Joe,

Can you please explain a bit more on what you mean by the following comment of yours.

"One of the critical working intrinsics of both Multi Load and Fast load are that they are AMP local. "

I'm not able to get a complete grasp of the concept of Multiload/Fastload being AMP local.

Regards,
Annal T
Enthusiast

Re: Join Index

Once mload get's into the APPLY phase, ie., when it begins to apply the input records onto the target table, the worktable contents of an AMP reflects the records that should go into the target table on the same AMP (ie there's no more rehashing into a different AMP). Also this is the reason why mload doesn't support USIs but supports NUSIs.. NUSIs are AMP-local. where as USI subtable contents are hash distributed. This means that to build the NUSI subtable of the target table, an AMP needs to work only on it's copy of the NUSI subtable. where as for an USI it would have had to talk to other AMPS. And talking to other AMPs are always a costly (if not expensive) affair over the bynet.