All Forums Database
JerryLusa 23 posts Joined 08/13
28 Mar 2015
Intentionally Stale Bookend Statistics to get past inconsistent right-side stats extrapolation by query optimizer

Here's a clever way to force the query optimizer to follow the same *stable* plan for a query regardless of the data set it runs against.  Useful when the data set cardinalities and row count profiles are well understood.  Also useful when ID numbers increase and the query optimizer would have to extrapolate past the right-most interval.

  1. create a set of parallel tables - call them bookend_<live table name>
  2. insert representative data sets into the bookend_xxxxxx tables with ID number changed to 0,1,2,3  and 1000001,1000002,1000003,10000004, or similar range that is larger than the range of ID values expected; these are the "bookends"
  3. collect the desired statistics on the bookend_xxxxxx tables (copy from live tables to start)
  4. copy the bookend_xxxxxx table statistics to the live tables: DROP STATS ON <live table>;  COLLECT STATS ON <live table> FROM <bookend table>
  5. when tuning, make changes to bookend_xxxxxx table stats, then repeat #4

An added benefit is the abilty to add new statistics on terabyte size tables in just a few minutes.  Query tuning can proceed rapidly.
Is anyone doing something like this or accomplishing it another way?

Jerry L.
You must sign in to leave a comment.