 |
 |
Archives of the TeradataForum
Message Posted: Fri, 12 Feb 2005 @ 04:37:35 GMT
| Subj: | | Re: Guidelines for using Distinct vs. group by |
| |
| From: | | Michael Larkins |
| | A subquery itself can legitimately return one or more values. | |
This is a true statement but only from the table(s) in the main query. However, if the subquery contains dups, the first would always be found
when it uses an equivalent of an IN list. Therefore, a dup would take up needless space and therefore, has always been eliminated. In the old
days a subquery aqctually did a DISTINCT operation in Teradata because this was the most efficient way to accomplish it, space wise when disks
were small (4MB in size).
Today, the optimizer eliminates duplicates without eliminating dups because it is more efficient. The Inclusion merge join means simply that
when the join step finds the first match, it stops looking for a secondary match for the table(s) referenced in the subquery. It would then move
to the next row from the main table and compare it to the previous matching row from the subquery values. This is the difference between a merge
join and an inclusion merge join - eliminates dups without actually doing a redistribution to eliminate dup values. Pretty ingenious!!
Hope this helps,
Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor
| |
