USI in multiload

Database
Enthusiast

USI in multiload

Hi,
This is a very basic question but why cant we use USI in multiload...
If somebody can explain it with an example would be great...
12 REPLIES
Junior Contributor

Re: USI in multiload

In MLoad's Application phase each AMP processes it's data locally.
NUSI-rows are AMP-local, so any modification is handled within that AMP.
But USI-rows are stored on a different AMP, thus that AMP has to communicate with two other AMP. The first AMP deletes the old row and the second AMP inserts the new row. This is way too much overhead.

Dieter
Enthusiast

Re: USI in multiload

Efficiently updating non-local objects such as secondary indexes and join indexes is indeed a more difficult problem than updating the base component of the table. However that very problem was successfully solved in the implementation of the standard SQL "merge into..." statement in TD 12.0. A decision was made not to invest the development resources to put that functionality into MultiLoad because we now consider SQL merge to be a better solution overall for most load applications than MultiLoad.
Enthusiast

Re: USI in multiload

Dieter,

the answer was great but i cannot understand the statement " One Amp will insert the row while other Amp will delete it".

We would appreciate if you can explain a bit more about it...

Cheers,
Novice
Junior Contributor

Re: USI in multiload

A USI is similar to a table with a UPI on the indexed column,
e.g.
create index usi (i int) on tab;

creates a subtable which is exactly the same as

create table usi(i int, -- indexed column
overhead byte(7), -- internal overhead, at least 7 bytes
baseROWID byte(8) -- ROWID of original row, 10 bytes if partitioned table
) unique primary index(i);

If the column i is modified, then there will be a new PI-value for that USI-subtable.
A new value results in a new RowHash, thus that row must be stored on a different AMP.

Dieter
Enthusiast

Re: USI in multiload

thanks Dieter..

Re: USI in multiload

Hi

I have a doubt regarding these indexes.Normally accessing rows through USI is a 2 AMP OPERATION and accessing rows through NUSI is an ALL AMP OPERATION.On that note,then MULTILOAD should be more fast with USI than NUSI.Could you please explain this.

Thanks
Archana
Enthusiast

Re: USI in multiload

USI is a two amp operation. Multiload application phase is local amp process. For NUSI the subtable is present in the same amp of the actual record, whereas in USI the data and subtable may remain in different amps. Hence USI is not allowed in MLOAD.
Enthusiast

Re: USI in multiload

Hi All,

Just a small clarification.  Why the subtable of the USI is directed to another AMP, instead it could be had in the same AMP as that of NUSI, which could increase performance (although the overhead of storing it on same AMP, however now the subtable row is stored in another AMP which is also overhead)

Thanks,

Cheeli

Junior Contributor

Re: USI in multiload

Hi Cheeli,

you're right, the access from USI row to base row would be faster when it's within the same AMP.

But this is only the step #2, as a first step you would need to do a broadcast to all AMPs in the system because the exact AMP is unknow. And this is much more overhead.

Dieter