Join Index
Posted by ~Ray @ 2007-09-24 14:54:36
This online forum is for user-to-user discussions of Teradata products and is not an official customer support channel for Teradata. If you demand enjoin assistance please communicate Teradata give.
Creating a join list 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 list / Secondary list subtable is also applied atomically by the Database.
connect list creates a displace delay and anychanges done to locate table automatically those changes will get reflect in list delay. Teradata take compassionate of this.1. How ever When you fill data through Multi fill and Fast fill if delay is having connect Index then it will through error.
hi,Thanks for the replies. If we can believe join list like views in oracle or sql server what makes it disappoint a multiload ????? Is there any reason which blocks multiload operation on the table which has join list?
Do your duty Dont expect the recognise. God ordain furnish the acquire for you
The fill utilities (multiload and fastload) were simply not designed to maintain join indexes. The fill utilities don't give 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 bulge using standard SQL statements such as attach or MERGE.
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?
One of the critical working intrinsics of both Multi Load and abstain load are that they are AMP local. That's One good reason for not supporting JI... But yes you can undergo a hit delay JI with same PI which would be AMP local so it doesn't answer the question why mload won't give 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 be indexes on an empty table anyway
.. probably instead of internally recreating all the indexes at the end of a fill they just shoved it off to the App dev guys. :o
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 fill and Fast load are that they are AMP local. "I'm not able to get a end hold of the concept of Multiload/Fastload being AMP local. Regards,Annal T
Once mload get's into the bear on phase ie. when it begins to apply the enter records onto the aim delay the worktable contents of an AMP reflects the records that should go into the target delay 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 delay an AMP needs to work only on it's copy of the NUSI subtable where as for an USI it would undergo had to talk to other AMPS. And talking to other AMPs are always a costly (if not expensive) affair over the bynet.[ADVERTHERE]Related article:
http://www.teradata.com/teradataForum/shwmessage.aspx?forumid=9&messageid=8786
0 Comments:
No comments have been posted yet!
|
