 |
 |
Archives of the TeradataForum
Message Posted: Wed, 24 Aug 2005 @ 15:40:35 GMT
| Subj: | | Re: Is USI useful for avoiding NUPI row check |
| |
| From: | | Victor Sokovin |
| | In order to avoid the NUPI duplicate row check, we should enforce uniqueness by defining a USI. This will enhance the performance. Also
Teradata document says that while inserting large number of records we should remove the SI and recreate them once the insertion is done which
will improve the performance. Isn't these two statement contradicting each other because if we will remove the SI while inserting, this will allow
duplicate row check. | |
You are right: the statements do contradict each other.
Dropping SI before your inserts/updates/deletes is a good idea and it helps reducing the DML run times. The downside (there are always
downsides, aren't there?) is that you might end up with duplicates. You will notice them immediately when you attempt to rebuild the SI.
Depending on how likely duplicates are you could set up your deduplication policy. You either check for duplicates each time before rebuilding
the SI (if duplicates are frequently loaded) or you dedupe if index rebuild failed (duplicates do not occur very often). You could also make
deduplication a part of your ETL process so that ETL guarantees that duplicates will never reach TD.
Regards,
Victor
| |
