Monday, 28 March 2011

When is DDL not DDL?

If you ever thought that the LAST_DDL_TIME on a table only tells you the last time that DDL was performed directly upon that table, then think again.

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.

6 comments:

  1. Peter,

    There's an awful lot of non intrusive commands that can cause last_ddl_time to update. Recompiling a procedure, moving a table (including ONLINE for IOT's), adding a comment to a table and many other non-structural changed

    ReplyDelete
  2. Hi Neil,

    Thanks for taking an interest.

    I probably shouldn't have used the word 'structural' - it was inaccurate for the example. I'll edit the post to make it clearer.

    I would expect moving a table or adding a comment on a table (or one of its columns) to change the last DDL time on the table. After all, we've performed some DDL against the table!

    However, my example below is the unexpected case that performing DDL against an INDEX updates the LAST_DDL_TIME for the index's parent table.

    I have just tested recompiling a procedure and it does NOT cause a change in LAST_DDL_TIME for any dependent table. I have amended the post to show this, as a counter example.

    Cheers,
    Pete

    ReplyDelete
  3. Pete,

    It simply piqued my interest - I recall doing some work on LAST_DDL_TIME about 18 months ago but that was with an old client and I don't have the details any more. I've never really trusted the LAST_DDL_TIME, but I do wonder why it's updating the parent table.

    The LAST_DDL_TIME on a table also updates on a CREATE INDEX or DROP INDEX command, so there must be some sort of additional dependency. I've done a quick trace check and tried with a DDL trigger but nothing obvious showing up. If you work out why, I hope you post it up!

    ReplyDelete
  4. Even granting SELECT access on a table will update its LAST_DDL_TIME. You can see what I mean at the URL below:

    http://international-dba.blogspot.co.uk/2010/11/grant-select-updates-lastddltime.html

    ReplyDelete
    Replies
    1. I can sort of understand changing grants on a table will change the LAST_DDL_TIME. It's not exactly changing the table definition, but it is changing permissions on that table, so I guess it qualifies.

      Delete