DB2_EXTENDED_OPTIMIZATION

Tuesday, June 03, 2008

I loved this new option 'ENHANCED_MULTIPLE_DISTINCT' that they gave for the registry variable in ver 9 fp 2. I normally face with sqls having multiple distinct clauses, select count(distinct(x)) , sum (distinct(x)) from table1, and what happens here is that the optimizer tries to do the groupby twice, once for each aggregation function and then unions them at the end.
Because of the new option we can prevent the above so that both functions are executed against the data whilst loading it only once.
This has good improvement potential in single processor systems and will not be useful all the time.

0 comments:

Post a Comment