unique secondary indexes while multiloading 1

[balajius2]

Hello all,

Does anyone know why Teradata doesnt support the target tables with unique secondary indexes while multiloading .

 

[dnoeth]

- a NUSI is AMP local, i.e. an index entry points to a row on the same AMP -> a modification is always AMP local

- a USI access is a two AMP access, i.e. an index entry points to a row on a different AMP -> a modification is not AMP local

Dieter

 

[balajius2]

thank you very much Dieter.
i almost got your point.It means that its not possible to update a table that has its rows or indexes spread across multiple AMP's.

 

[dnoeth]

Almost

Every table has it's rows spread across all AMPs. But a NUSI indexes only the rows which are located on the same AMP, so a modification of an index value can be done AMP local, too.

To update a USI there has to be some communication between the AMP with the original data row and the AMP with the index row, that's not allowed/possible with MLoad.
This is the same reason why an update on the primary index columns is not allowed in MLoad, a new PI value results in a new rowhash, which results in a new AMP were that row has to be stored (equal to delete/insert that row)

Dieter

 

[balajius2]

Now its very clear to me .
Thank you very much for your help.
If possible, please let me know if there are any materials
that i can go through to get familiar with this kind of stuff.

Thanks again,
Ben.

 

[dnoeth]

You'll find all Teradata manuals at:

http://www.info.ncr.com

-> Teradata Data Warehousing

It's recommended to download the whole manual CD under "CD-ROM List and Images", this includes a global index for searching.

There's an "Introduction to Teradata Warehouse" for an overview and "Database Design" for details on indexes and internal structures...

Dieter

 

[balajius2]

thank you again.