DISTINCT and GROUP BY
For cases when the DISTINCT is semantically equivalent to GROUP BY, the optimizer makes a cost-based decision to eliminate duplicates either by way of a combined sort and duplicate elimination or an aggregation step.
DISTINCT Operator and UDTs
If you specify the DISTINCT operator and the select list specifies a UDT column, the database applies the ordering functionality of that UDT to achieve distinctness.
- x INTEGER
- y INTEGER
- r INTEGER
Suppose that the external type for CircleUdt is a character string containing the ASCII form of the x, y, and r components. The ordering functionality is defined to map and compare the r components of two instance of CircleUdt.
- NEW CircleUdt('1,1,9'), where x=1, y=1, and r=9
- NEW CircleUdt('6,10,9')
The system sometimes returns the '1,1,9' result and at other times returns the '6,10,9' result as part of the DISTINCT result set.
DISTINCT Operator and Large Objects
You cannot include LOB columns in the select list of a SELECT request if you also specify DISTINCT.
You can specify LOB columns with DISTINCT if you CAST them to an appropriate data type, as documented in this table:
IF the LOB column has this data type … | THEN you can reference it with DISTINCT if you CAST it to this data type … |
---|---|
BLOB |
|
CLOB |
|
SQL Elements That Cannot Be Used With a DISTINCT Operator
- WITH request modifier
- TOP n operator
- The recursive statement of a recursive query
However, you can specify DISTINCT within a nonrecursive seed statement in a recursive query.
Unexpected Row Length Errors With the DISTINCT Operator
- A data row is too long.
- Maximum row length exceeded in database_object_name.
For explanations of these messages, see Teradata Vantage™ - Database Messages, B035-1096.
The BYNET only looks at the first 4,096 bytes of the sort key created to sort the specified fields, so if the column the sort key is based on is greater than 4,096 bytes, the key is truncated and rows in the set can either be identified as duplicates when they are not or identified as unique when they are duplicates.