Why MultiLoad Utility supports only Non Unique Secondary
Index(NUSI) in the Target Table ?




Answers were Sorted based on User's Feedback



Why MultiLoad Utility supports only Non Unique Secondary Index(NUSI) in the Target Table ?..

Answer / madhu

Like FastLoad, MultiLoad does not support Unique Secondary
Indexes (USIs). But unlike FastLoad, it does support the
use of Non-Unique Secondary Indexes (NUSIs) because the
index subtable row is on the same AMP as the data row.
MultiLoad uses every AMP independently and in parallel. If
two AMPs must communicate, they are not independent.
Therefore, a NUSI (same AMP) is fine, but a USI (different
AMP) is not.

Is This Answer Correct ?    29 Yes 6 No

Why MultiLoad Utility supports only Non Unique Secondary Index(NUSI) in the Target Table ?..

Answer / rajesh kumar s

MulitLoad has support for Non-Unique Secondary Indexes
(NUSIs) since NUSIs are AMP Local. They have subtable within
base table. It has no hash bucket to redirect to another
amp, everything points to their own amp (AMP Local)

If you go with USI, it has hash bucket, which might redirect
to another amp, more possibilites for full table scan.

Motive for avoiding Indexes(other thn NUSI): Avoid Full
table scan.

Regards
Rajesh Kumar S

Is This Answer Correct ?    8 Yes 0 No

Why MultiLoad Utility supports only Non Unique Secondary Index(NUSI) in the Target Table ?..

Answer / satya

whenever we define a SI an SI subtable is created in each
AMP.For USI they go for a hash distribution, and hence the
actual data row pointed by the USI subtable rows in one AMP
may not be in the same AMP as the subtable..so the AMPs
have to communicate, which is not supported by Multiload.
For NUSI the subtable will store references of only those
actual data rows who exist in the same AMP as the
subtable..they all point to the data in their own
AMP..hence AMPs dont need to communicate here.

Thus the AMPs work in parallal with NUSI and hence Mload
supports that.

Is This Answer Correct ?    6 Yes 1 No




Why MultiLoad Utility supports only Non Unique Secondary Index(NUSI) in the Target Table ?..

Answer / priya

Fload It self will not support Secondarys Indexs concept
becuase Fload stands for Fast load so.. it will not proceed
futhur in case the table is defined with Secondary indexes
we need to drop the Secondary index in case if we need to
proceed furthur.

So where is the concpets USI and Non-Unique secondaey
indexes in Fload.

Regards,
Priya K

Is This Answer Correct ?    5 Yes 1 No

Why MultiLoad Utility supports only Non Unique Secondary Index(NUSI) in the Target Table ?..

Answer / tdguy

When it comes to secondary indexes, subtables are build by
TD.
In USI, the subtables are build on all the amps and used,
when this index is used in the query. Since, this involves
communication of amps, mload does not support usi.
In NUSI, the subtables are build within the amps and refer
to the local data of the amp. Thus, NUSI is supported by
mload, since it is amp local.

Is This Answer Correct ?    5 Yes 1 No

Why MultiLoad Utility supports only Non Unique Secondary Index(NUSI) in the Target Table ?..

Answer / krish

i also dnt know exactly..cal u help me more

Is This Answer Correct ?    0 Yes 0 No

Why MultiLoad Utility supports only Non Unique Secondary Index(NUSI) in the Target Table ?..

Answer / hari m

WHAT EVER WE DISCUSSED ABOVE IS OK WITH THE MLOAD.

EVEN I AGREE WITH MLOAD, FLOAD HAS LIMITIATON AS USI, JI,
TRIGGERS MULITIPLE AMP OPERATIONS.


I have a quick question why FLOAD doesn't supports NUSI? As
posted above if Nusi is operations are within in same amp.

Please explain in this regard.

Is This Answer Correct ?    0 Yes 0 No

Why MultiLoad Utility supports only Non Unique Secondary Index(NUSI) in the Target Table ?..

Answer / satish

When table is having NUSI, the sub table created by it will
be populated only by Hashing (not like Hash Re distribution
for USI), here redistribution of the records will not be
done,which will take more time generally,

As Fastload and Multiload are intended to Load the data at
fast they have taken out the USI, support so that the target
tables will be loaded very faster,

if we need we can create the SI after loading

---
If you have better ANS please Share with us

Is This Answer Correct ?    2 Yes 3 No

Why MultiLoad Utility supports only Non Unique Secondary Index(NUSI) in the Target Table ?..

Answer / hardeep

Hi Madhu

Thanks for ur response but could u pls explain ur answer in more detail, if possible with an example?

Thanks again.

Is This Answer Correct ?    1 Yes 5 No

Post New Answer



More Teradata Interview Questions

Give a justifiable reason why Multi-load supports NUSI instead of USI.

0 Answers  


If the PMON is not working then how do you manage and monitor all processes, resources and sessions etc.

0 Answers  


Suppose i have data like 2009/12/31 555555534 ' ' Generally last 2 records are wrong format it is going to error table but i don't want load error table if any wrong format in Date column Pass "NULL" how can we do it fast load

4 Answers  


How is the teradata different from oracle?

0 Answers  


What exactly do you know about catching in teradata?

0 Answers  






1)WHAT IS THE EXACT DIFFERENCE BETWEEN TERADATA AND ORACLE? 2)MOST OF THE COMPANIES ARE USING TARA DATA FOR "DWH PROJECTS" ONLY? WHY?

6 Answers   Wipro, Bank Of America,


What are the things to be considered while creating secondary index?

0 Answers  


what is identity columns in TD?

2 Answers   Wipro,


Explain about Skew Factor?

6 Answers   TCS,


What is the difference between fastload and multiload? Which one is faster?

0 Answers  


List the logical and conditional operators that are used with teradata along with their meanings?

0 Answers  


What is bteq utility in teradata?

0 Answers  






Categories