Re: Dependencies and invalidations.

From: <fitzjarrell_at_cox.net>
Date: 22 Apr 2006 18:47:08 -0700
Message-ID: <1145756828.459481.136330@i39g2000cwa.googlegroups.com>

Mladen Gogala wrote:
> On Sun, 23 Apr 2006 02:20:10 +0200, Maxim Demenko wrote:
>
> > Mladen Gogala schrieb:
> >> In Oracle RDBMS 10.2 it is practically impossible to invalidate a PL/SQL
> >> object. I tried with adding indexes, dropping primary key
> >
> > Not every ddl invalidate the dependent procedures, only changing layout
> > of tables ( i.e. add/drop/modify columns, maybe something else, but not
> > add/drop constraint, alter table move etc.)
>
> Those DDL can change plans of the underlying SQL statements and,
> therefore, should invalidate the dependent procedures.
>
>
> >
> > and analyzing
> >> the underlying tables,
> >
> > Here you are joking - right ?
>
> Nope. No jokes here. Analyzing the underlying tables can also drastically
> change plans and should, therefore, invalidate the procedures.
>
>
> >
> > but the PL/SQL procedure build on top of the table
> >> remained valid. Oracle 9i PL/SQL objects were much more sensitive.
> >
> > You probably don't have 9i instance right now to test, the behaviour is
> > the same in 9i and even 8i.
>
> You are right, I don't have access right now. I will check it on Monday,
> though.
>
>
> --
> http://www.mgogala.com

The same behaviour, exactly, is exhibited in 9.2.0.6:

SQL> create table emp1 as select * from emp;

Table created.

SQL>
SQL>
SQL> create or replace function sum_nomgr (d number) return number

  2 as
  3 total_sal number(10,3);
  4 begin
  5 select sum(sal) into total_sal
  6 from emp1
  7 where deptno=d and
  8 job != 'MANAGER';
  9 return(total_sal);
 10 end;
 11 /

Function created.

SQL> select object_name, status
  2 from user_objects
  3 where object_name in ('EMP1','SUM_NOMGR')   4 /

OBJECT_NAME

---

STATUS

---

EMP1
VALID SUM_NOMGR
VALID SQL> alter table emp1 add constraint emp1_pk primary key(empno);

Table altered.

SQL> select object_name, status
  2 from user_objects
  3 where object_name in ('EMP1','SUM_NOMGR')   4 /

OBJECT_NAME

---

STATUS

---

EMP1
VALID SUM_NOMGR
VALID SQL> analyze table emp1 compute statistics for table for all indexed columns;

Table analyzed.

SQL> select object_name, status
  2 from user_objects
  3 where object_name in ('EMP1','SUM_NOMGR')   4 /

OBJECT_NAME

---

STATUS

---

EMP1
VALID SUM_NOMGR
VALID SQL> create index emp1_deptno_i on emp1(deptno);

Index created.

SQL> analyze table emp1 compute statistics for table for all indexed columns;

Table analyzed.

SQL>
SQL>
SQL> select object_name, status

  2 from user_objects
  3 where object_name in ('EMP1','SUM_NOMGR')   4 /

OBJECT_NAME

---

STATUS

---

EMP1
VALID SUM_NOMGR
VALID SQL> alter table emp1 drop constraint emp1_pk;

Table altered.

SQL>
SQL>
SQL> select object_name, status

  2 from user_objects
  3 where object_name in ('EMP1','SUM_NOMGR')   4 /

OBJECT_NAME

---

STATUS

---

EMP1
VALID SUM_NOMGR
VALID SQL> spool off

David Fitzjarrell Received on Sat Apr 22 2006 - 20:47:08 CDT