DB2 v11 SQL: Remove Duplicates Enhancement

Enhanced duplicate removal:

– 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:

  • Single MAX or MIN aggregate function
  • GROUP BY with a single MAX or MIN function
  • Certain non-correlated subqueries (not transformed)
  1. Additional Info DB2
  2. Read more here

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.