-
Time when Index was last rebuilt
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
-
Code:
SELECT last_analyzed
FROM dba_tables
WHERE UPPER(OWNER) LIKE '%XYZ%'
AND UPPER(TABLE_NAME) LIKE '%ABC%';
You only would want to rebuild the indices when they are fragmented.
-Sam
Thanx
Sam
Life is a journey, not a destination!
-
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.
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
-
Originally posted by sambavan
You only would want to rebuild the indices when they are fragmented.
-Sam
...and if moving them to a different tablespace.
OCP 8i, 9i DBA
Brisbane Australia
-
Originally posted by grjohnson
...and if moving them to a different tablespace.
..and if you made them unusable to load data faster, then rebuilt them after the load was complete.
Any more?
-
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.
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
-
... 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.
-
Originally posted by TomazZ:
Rebuild gets timestamped in ???_objects.last_ddl_time
I am afraid that's not true:
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
Last edited by Raminder; 07-30-2003 at 11:40 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|