Re: JDBC, prepared queries, and partitioning

Simon Riggs wrote
> ISTM we need a way to say "this query's parameters vary wildly at
> execution time, so I really need to re-plan it every time".

If the wild variations don't affect the cost estimate, they won't affect the plan?

Isn't it more a case of
"even a minor variation in just one of this query's parameters can result in wildly different cost estimate,
so I really need to re-plan it every time."

This is something that the database may be able to determine from currently collected statistics.

However I suspect that any means of trying to automatically determine that would *always* result in
"Yep, minor changes can wildly affect the cost estimate"

(I'm ignorant of details and history but...)

Could this be added (conditionally):
With the stored plan, store the cost estimate,
Re-estimate the cost of the chosen plan with the newly supplied parameters,
If the new cost is significantly higher then re-plan.

Checking the cost of a plan every time should be a lot cheaper than always re-planning.

I can already see problems with this...
the query will get slower and slower over time as it settles onto the plan that best handles the worst cases.

Stephen Denne.

Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way.

__________________________________________________________________
This email has been scanned by the DMZGlobal Business Quality
Electronic Messaging Suite.
Please see http://www.dmzglobal.com/services/bqem.htm for details.
__________________________________________________________________