Re: Analyze /LMT Question .....

From: Tanel Poder <tanel_at_@peldik.com>
Date: Mon, 24 Feb 2003 03:17:47 +0200
Message-ID: <3e597215$1_2@news.estpak.ee>

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.

Erm.. what is this? I can't agree with that. It would be quite nonsense to recompile hundreds or thousands objects after every analyze in some apps.

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.

See below,
Tanel.

SQL> desc a;

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
----
 I                                                  NUMBER

SQL> create or replace procedure p
as
v number;
begin
select i into v from a where rownum < 2; end;
/
  2 3 4 5 6 7
Procedure created.

SQL> select status from user_objects where object_name = 'P';

STATUS

---

VALID SQL> analyze table a compute statistics;

Table analyzed.

SQL> select status from user_objects where object_name = 'P';

STATUS

---

VALID SQL> alter index i rebuild;

Index altered.

SQL> select status from user_objects where object_name = 'P';

STATUS

---

VALID SQL> alter table a add j number;

Table altered.

SQL> select status from user_objects where object_name = 'P';

STATUS

---

INVALID SQL> Received on Sun Feb 23 2003 - 19:17:47 CST