Hi
I was wondering if there is any way to know when was the last time an index was rebuilt in the database (except for using log-miner).
Similarly, when was the last time a table was analysed?
Thanks
Raminder
Printable View
Hi
I was wondering if there is any way to know when was the last time an index was rebuilt in the database (except for using log-miner).
Similarly, when was the last time a table was analysed?
Thanks
Raminder
look in user_indexes and user_tables for the last_analyzed time
You only would want to rebuild the indices when they are fragmented.Code:
SELECT last_analyzed
FROM dba_tables
WHERE UPPER(OWNER) LIKE '%XYZ%'
AND UPPER(TABLE_NAME) LIKE '%ABC%';
-Sam
Thanks davey and Sambavan.
Is there a way to know when were the indexes last rebuilt?
Raminder
Rebuild gets timestamped in ???_objects.last_ddl_time.
But it will be overwritten by other DDL on index.
...and if moving them to a different tablespace.Quote:
Originally posted by sambavan
You only would want to rebuild the indices when they are fragmented.
-Sam
..and if you made them unusable to load data faster, then rebuilt them after the load was complete.Quote:
Originally posted by grjohnson
...and if moving them to a different tablespace.
Any more?
:cool:Quote:
Originally posted by slimdave
..and if you made them unusable to load data faster, then rebuilt them after the load was complete.
Any more?
... and after alter table exchange partition ... excluding indexes.
... 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.
I am afraid that's not true:Quote:
Originally posted by TomazZ:
Rebuild gets timestamped in ???_objects.last_ddl_time
SQL> select object_name, object_type, last_ddl_time from user_objects where object_name= 'MCF_EXCEPTION_LOOKUP_1';
OBJECT_NAME OBJECT_TYPE LAST_DDL
------------------------------ --------------- --------
MCF_EXCEPTION_LOOKUP_1 INDEX 03-02-21
SQL> alter index MCF_EXCEPTION_LOOKUP_1 rebuild;
Index altered.
SQL> select object_name, object_type, last_ddl_time from user_objects where obj
ect_name= 'MCF_EXCEPTION_LOOKUP_1';
OBJECT_NAME OBJECT_TYPE LAST_DDL
------------------------------ --------------- --------
MCF_EXCEPTION_LOOKUP_1 INDEX 03-02-21
My envronment is Oracle 8.0.5.2.1, though.
Regards
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
No Johnson. I guess its a different version of Oracle we are using. You are on 8i, I guess while I am ancient(8).Quote:
Originally posted by grjohnson:
Yeah but was it already rebuild today? Stick a TO_CHAR around the last_ddl_time
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
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.
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 ?
:confused: :p :confused:
Moving has been mentioned before.Quote:
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.
Bottom line, it looks like we are rebuilding our indexes all the time.
This sounds familiar... index rebuilding... Windows rebooting... :D
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 ;)
Yet another, "when you dont need Index on the COL list" :DQuote:
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.