On Fri, Mar 4, 2011 at 8:47 AM, Mark Thornton <mthornton@optrak.co.uk> wrote:
> It is a temporary table and thus I hadn't thought to analyze it. How should
> such tables be treated? Should I analyze it immediately after creation (i.e.
> when it is empty), after filling it or ... ? The expected usage is such that
> the temporary table will have less than 100 or so rows.
When in doubt, analyze.
If you're engaging in OLAP, or some other workload pattern that
involves writing many small batches, then analyzing all the time is
bad. If you're engaged in any workload that writes rarely - a bulk
insert here, a create-table-as-select there, etc - always analyze.
The cost of analyzing when you shouldn't is low and O(1) per analysis,
and the cost of not analyzing when you should have can easily be
O(n^2) or worse w/r/t data size.
The cost of analyzing is especially low on a temp table only owned by
your current session, because no one else will be disturbed by the
table lock you acquire if you context-switch out before it's done.
-Conor