Time when Index was last rebuilt

[grjohnson]

Yeah but was it already rebuild today? Stick a TO_CHAR around the last_ddl_time

select object_name, object_type, to_char(last_ddl_time, 'DD/MM/YYYY HH24:MI:SS')
from user_objects where object_name= 'TEST_INDEX';

TEST_INDEX,INDEX,06/06/2003 17:17:00

alter index TEST_INDEX rebuild;

select object_name, object_type, to_char(last_ddl_time, 'DD/MM/YYYY HH24:MI:SS')
from user_objects where object_name= 'TEST_INDEX';

TEST_INDEX,INDEX,30/07/2003 17:18:18

[Raminder]

Originally posted by grjohnson:
Yeah but was it already rebuild today? Stick a TO_CHAR around the last_ddl_time

No Johnson. I guess its a different version of Oracle we are using. You are on 8i, I guess while I am ancient(8).

SQL> select object_name, object_type, to_char(last_ddl_time, 'DD/MM/YYYY HH24:MI
:SS')
fr 2 om user_objects where object_name= 'MCF_EXCEPTION_LOOKUP_1';

OBJECT_NAME OBJECT_TYPE TO_CHAR(LAST_DDL_TI
------------------------------ --------------- -------------------
MCF_EXCEPTION_LOOKUP_1 INDEX 21/02/2003 16:18:30

SQL> alter index MCF_EXCEPTION_LOOKUP_1 rebuild;

Index altered.

SQL> select object_name, object_type, to_char(last_ddl_time, 'DD/MM/YYYY HH24:M
I:SS') from user_objects where object_name= 'MCF_EXCEPTION_LOOKUP_1';

OBJECT_NAME OBJECT_TYPE TO_CHAR(LAST_DDL_TI
------------------------------ --------------- -------------------
MCF_EXCEPTION_LOOKUP_1 INDEX 21/02/2003 16:18:30


Raminder

[marist89]

Yes, it's a version thing. I had the same problem on 8.1.5 (which was a bug) and was eventually fixed in 8.1.7.x.

[cruser3]

What tomaz said seems to be right - if you query dba_objects.last_ddl_time u can get it(last idx rebuild after running an "alter idx_name rebuild" - but if any other ddl has occurred since it may overwrite)!

last_analyzed is updated only when you ANALYZE a table - the table, index, cluster gets analyzed and the time is logged as last_analyzed
in user|dba_tables !

Any other ideas ?

[TomazZ]

Originally posted by slimdave
... and when the table is moved.

It used to be the case that making any column nullable or non-nullable would make all the bitmap indexes unusable, so there's another one.

Moving has been mentioned before.

Bottom line, it looks like we are rebuilding our indexes all the time.
This sounds familiar... index rebuilding... Windows rebooting...

Raminder: when you will get your database upgraded, you will be able to put a trigger on DDL - enabling you to get 100% rebuild history

[abhaysk]

Originally posted by slimdave
... and when the table is moved.

It used to be the case that making any column nullable or non-nullable would make all the bitmap indexes unusable, so there's another one.

Yet another, "when you dont need Index on the COL list"