Re: Analyze /LMT Question .....

On Mon, 24 Feb 2003 03:17:47 +0200, Tanel Poder wrote:

> Hi,
>

>> You do not *need* to recompile everything, but procedures etc. referencing
>> tables for which you've re-collected statistics or rebuilt indexes will be
>> invalidated, automatically.

Depends what you think you are agreeing or disagreeing with, really. Your examples are of procedures etc. and their state of validity as procedures. Which is fair enough, and if that's what you thought I meant, then I'm at fault for writing it open to that interpretation. There was, however, an 'etc' there, meaning that I was actually referring to anything already loaded in thelibrary cache, for which an execution plan has already been compiled.

Those most definitely will be invalidated by DDL, create index, or fresh analysis of statistics (and a good thing too, of course, since new indexes or new statistics could well mean that a better plan could be derived given half a chance).

Those execution plans are invalidated, and will be recompiled the first time they are referenced following the invalidation (second time if they involve remote calls).

> It would be quite nonsense to recompile hundreds or thousands objects after
> every analyze in some apps.

It might be nonsense, but that's exactly what happens in the library cache.

> DDL on an object invalidates it's dependent objects. I don't think any
> object can be dependent on an index for example. And analyze isn't DDL.

It's in the same class of statements as DDL, namely: it invalidates any execution plans in the library cache referencing the object for which new statistics have just been computed/estimated.

HJR Received on Sun Feb 23 2003 - 20:46:40 CST