 |
 |
Archives of the TeradataForum
Message Posted: Tue, 17 Aug 2004 @ 08:26:10 GMT
| Subj: | | Re: UNION And INSERT INTO |
| |
| From: | | Victor Sokovin |
"Christie, Jon" wrote:
| | Even if the target table is initially empty, duplicate row checking will still have to be performed. Why? Because the UNION only
guarantees that there will be no duplicate rows for ALL COLUMNS. This does not guaranteed that some subset of columns (like the PI of the target
table) will be unique. | |
I thought the original question was more on the all-column duplicates as checked by SET tables. UNION dedupes in the spool file and we might
expect smaller SET performance overhead in this particular case.
However, there might be situations when the overhead would still be significant. UNION dedupes the SELECT output in the spool file but there
still might be a data type conversion in the actual INSERT phase. If the conversion involves things like rounding there is a chance that
duplicates will appear as the result of such rounding. So, a new duplicate check is necessary before the result set will be inserted into the SET
table. Such things are not in the explain or any other readily available sources so I think it is easier to measure their potential overhead in
each particular case. If in this example data type conversion is indeed a factor of importance, there could be cases when the data types do not
need to be converted (fast insert phase) and there could be cases with a lot of conversions.
If there are other means to measure such things I'd like to hear about them.
Regards,
Victor
| |
