Archives of the TeradataForum
Message Posted: Fri, 13 Aug 2004 @ 19:17:59 GMT
| Subj: | | Re: UNION And INSERT INTO |
| |
| From: | | Dieter Noeth |
Bach, Michael wrote:
| | Taking an example of 2 SELECT statements combined by the UNION operator and being inserted into a table defined as CREATE SET. | |
| | The EXPLAIN will detail the elimination of duplicate rows in spool between the 2 SELECT statements. | |
| | My query to anyone who would know is whether this then removes the duplicate row checking process when the rows are inserted into the
CREATE SET table? or would this step still take place because of the table definition. | |
| | The destination table would be empty and I'm aware there would be other ways of doing this. The query is purely to do with how the UNION
and INSERT into CREATE SET table combine and whether the processing is clever enough to identify that duplicate row checks have already been
performed within the UNION step. | |
The optimizer will skip the duplicate row step if the target table is empty. The optimizer knows about uniqueness if:
- there's a Distinct
- or a uniquely indexed column
- or all columns from a set table are selected
This is true at least in V2R5, i can't remember in which release the optimizer started to do that.
Just try it as Viktor suggested...
Dieter
| 
