 |
 |
Archives of the TeradataForum
Message Posted: Sat, 23 Feb 2002 @ 09:43:39 GMT
| Subj: | | Re: NUPI considerations |
| |
| From: | | Dieter N�th |
| | Performance would be greatly improved if I modified the large table to have a NUPI of col2, I'd be doing PI joins to the two tables.
However the NUPI rows/value would be about 1000. There would be fairly even distribution across amps with each value have close to the same
number of rows/value (no spiking) | |
| | My row size is fairly small ~50 bytes. I'll have to check with the application team doing the MLOADs, but they should be able to
ensure uniqueness via the load (one of the PK columns is date). | |
| | Is your experience that using Multiset without adding a USI will still achieve good performance? It's a weekly MLOAD that adds 15
million rows at a time. I had some concerns with the NUPI Duplicate row check that would take place. | |
Don't try a NUPI with 1000 rows/value _without_ Multiset, there will be too much duplicate row checks. If you add a USI to enforce
uniqueness, it must be dropped before load. During load the table will be MULTISET, so no duplicate row checks, when you recreate the USI
it will be SET again. And if you load a PK-duplicate, you'll get an error when you recreate the USI, so this is similar to loading a
duplicate row. If there'll probably be no PK violation during load, don't create the USI, it's a large overhead in diskspace and
maintenance.
Dieter
| |
