I would like to find out when my indexes were last rebuilt.
From which data dictionary table I can get that information.
I understand last_analyzed, created and timestamp can be found in
USER_INDEXES and USER_OBJECTS but anywhere I can
locate date for last rebuilt?
There is only one sound argument for rebuilding an index:
Will the total cost of rebuilding the index be a reasonable price to pay for the resulting benefit to the system?
The answer to this question is frequently a resounding NO. In fact, sometimes the overall impact of rebuilding an active index will be detrimental to the system. However, there are still plenty of misconceptions about indexes that result in DBAs the world over wasting valuable time and effort rebuilding indexes unnecessarily.
BUT, if you have a regular window when the system is not in use, have a simple batch job that runs in that window without putting pressure on other batch tasks, and that batch job can never fail — then you might as well rebuild all the “safe” indexes you want to; there is often a slight performance gain to be had, and if it costs you nothing, then you might as well take it.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
This is an academical article having lost of assumptions (very right ones inedeed). I have the same views with him (we had an interesting talk last month) but hei: there is one non-mentioned good reason why to rebuild indexes: users very often create the indexes into the default tablespace which is a differenet mount point then the index one. At some point you have the data mount point 90% full and the index mount point only 20%.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g