We recently noticed some tables which appeared to have been recently changed, yet we couldn't find any related DDL changes.
Then we remembered that we regularly rebuild a handful of indexes (once a week, on Sunday morning) for performance/plan-stability reasons.
Could it be that rebuilding an index resets the LAST_DDL_TIME for the index's table?
Let's find out.
SQL> create table poo (x date);
Table created.
SQL> create index poodex on poo(x);
Index created.
SQL> select object_name,
to_char(last_ddl_time, 'DD-Mon-YYYY HH24:MI:ss')
2 from dba_objects where object_name like 'POO%';
OBJECT_NAME TO_CHAR(LAST_DDL_TIM
----------- --------------------
POO 22-Mar-2011 10:09:51
POODEX 22-Mar-2011 10:09:51
SQL> alter index poodex rebuild online;
Index altered.
SQL> select object_name,
to_char(last_ddl_time, 'DD-Mon-YYYY HH24:MI:ss')
2 from dba_objects where object_name like 'POO%';
OBJECT_NAME TO_CHAR(LAST_DDL_TIM
----------- --------------------
POO 22-Mar-2011 10:11:31
POODEX 22-Mar-2011 10:11:31
Yup! In a surprising (to me) result, it looks as though rebuilding an index does indeed reset LAST_DDL_TIME on the table, even though we are not performing DDL on the table directly.
In my opinion, this is a misleading result.
Thanks to Matt Penny for doing all the work and investigation and telling me to publish the results!
EDIT: Thanks to Neil below for suggestions of other events which may or may not cause this behaviour.
I have tested creating, amending, re-compiling and dropping a procedure, but this does not cause a change in LAST_DDL_TIME for the dependent table.
Example as follows:
14:16:55 PETE @ ETEST1D > create table foo (foocol number(5));
Table created.
14:17:20 PETE @ ETEST1D > @lastddl
OBJECT_NAME TO_CHAR(LAST_DDL_TIME,'DD-MON-
------------------------------ ------------------------------
FOO 28-Mar-2011 14:17:20
14:18:35 PETE @ ETEST1D > create procedure fooproc is
14:19:13 2 v_var varchar2(10);
14:19:26 3 begin
14:19:31 4 null;
14:19:38 5 exception
14:19:42 6 when others then null;
14:19:45 7 end;
14:19:47 8 /
Procedure created.
14:19:50 PETE @ ETEST1D > @lastddl
OBJECT_NAME TO_CHAR(LAST_DDL_TIME,'DD-MON-
------------------------------ ------------------------------
FOO 28-Mar-2011 14:17:20
FOOPROC 28-Mar-2011 14:19:50
14:20:06 PETE @ ETEST1D > create or replace procedure fooproc is
14:20:31 2 v_var varchar2(10);
14:20:34 3 begin
14:20:36 4 select to_char(foocol)
14:21:02 5 into v_var
14:21:07 6 from foo;
14:21:14 7 exception
14:21:17 8 when others then null;
14:21:18 9 end;
14:21:19 10 /
Procedure created.
14:21:20 PETE @ ETEST1D > @lastddl
OBJECT_NAME TO_CHAR(LAST_DDL_TIME,'DD-MON-
------------------------------ ------------------------------
FOO 28-Mar-2011 14:17:20
FOOPROC 28-Mar-2011 14:21:20
14:21:31 PETE @ ETEST1D > alter procedure fooproc compile;
Procedure altered.
14:22:07 PETE @ ETEST1D > @lastddl
OBJECT_NAME TO_CHAR(LAST_DDL_TIME,'DD-MON-
------------------------------ ------------------------------
FOO 28-Mar-2011 14:17:20
FOOPROC 28-Mar-2011 14:22:07
14:22:13 PETE @ ETEST1D > drop procedure fooproc;
Procedure dropped.
14:22:40 PETE @ ETEST1D > @lastddl
OBJECT_NAME TO_CHAR(LAST_DDL_TIME,'DD-MON-
------------------------------ ------------------------------
FOO 28-Mar-2011 14:17:20
Despite creating and dropping procedure, the LAST_DDL_TIME of the depdendent table is not changed. Exactly as expected.
Now trying with a comment (which is a DDL command):
14:24:20 PETE @ ETEST1D > comment on table foo is 'FOO is a test table';
Comment created.
14:24:25 PETE @ ETEST1D > @lastddl
OBJECT_NAME TO_CHAR(LAST_DDL_TIME,'DD-MON-
------------------------------ ------------------------------
FOO 28-Mar-2011 14:24:25
Again - as expected. A DDL command against the table changes the LAST_DDL_TIME.
This makes it all the more surprising that running a DDL command against an index will change LAST_DDL_TIME for its parent table.
