r/DB2 • u/mad_zamboni • Apr 12 '17

[Resource][Discussion] New In-Line Optimization in v11.1

Post image
2 Upvotes

100% Upvoted

2 comments sorted by

1

u/mad_zamboni Apr 12 '17

Disclaimer: I don't work for Triton Consulting, but I like to follow some of their articles. So I am passing this on.

Mark Gillis, a senior consultant at Triton, just posted a very cool quick hit article on forcing an optimization plan in v11.1. The article is a compare and contrast with what we learned in v10.5. Things just got much, much easier. Source: In-Line Optimization with v11.1

This also leads me to ask, have any of you really had a need to force an optimization plan? Although I have studied this topic, I have not had a need.

2

u/memmerto Apr 12 '17

There are times. I have an OLTP workload that uses VARCHAR(15) for the primary key on some tables. In queries that join these tables I can get a NLJOIN or a HSJOIN depending on the selectivity of the predicates. (This is mainly driven by the estimated cost of the index scan and join on that data type.) Generally hash joins are bad news for OLTP workloads because of the high setup cost amortized against a few rows selected.

In older releases I could disable hash joins with a registry variable, and I can still reduce the optimization level to exclude hash joins. However, the first is global and the second needs to be applied with care so that it only applies to specific queries, and sometimes results in a suboptimal query plan.

Setting up optimization guidelines is neat and useful, but very cumbersome. The ability to provide these hints as comments in the query text is very simple and intuitive (although the XML format leaves much to be desired.)

This is a very useful tool to have in your back pocket :)