– Lots of queries require duplicate removal: e.g. DISTINCT, GROUP BY, etc.
– Dup elimination via sorting can be expensive
– New techniques: Index duplicate removal, early out
Index duplicate removal:
One feature that the IDUG team has been investigating is improvements to Unique/DISTINCT and GROUP BY processing. This is an enhancement that will be realized the next time a statement is compiled (prepare or bind) after migrating to DB2 11, and the improvements can be significant, but it’s not something that is easily detectable by the average developer or DBA.
Certain SQL statements will be impacted, such as:
- SELECT DISTINCT
- GROUP BY
- Single MAX or MIN aggregate function
- GROUP BY with a single MAX or MIN function
- Certain non-correlated subqueries (not transformed)