Index Rebuild

[cchiara]

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?

Thanks in advance!

[stmontgo]

last_ddl_time in user_objects

steve

[manodba]

Doubt: Why would anyone want to know what time index is rebuild?

[cchiara]

Thanks for the info!

I was setting up a job to rebuild indexes weekly.
Just want to verify that the indexes are rebuilt as scheduled.

[slimdave]

Originally posted by cchiara
Thanks for the info!

I was setting up a job to rebuild indexes weekly.
Just want to verify that the indexes are rebuilt as scheduled.

Get your asbestos underpants on my friend, 'cos you are in for a flaming.

Here is the kind version:

"Rebuilding indexes weekly is a waste of time, and may even be harmful to the performance of your system".

[jmodic]

"When should you rebuild an index" by Jonathan Lewis:
http://www.dbazine.com/jlewis14.shtml

Conclusion

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.

[abhaysk]

Well, i see only one significant use of rebuild is some space will be freed up...but if u are gonna rebuild every now and then, i see no point..

Rebuild once in a quarter or even half yearly will suffice and you can get some space freed up..

We recently rebuilt our indexes (After nearly 8 months) and we got mere 10GB space freed up..so what good will it make if you rebuild every week?

Abhay.

[balajiyes]

Hi abhaysk
In some projects we do... we rebuild the fact tables every day aft every upload.
any hint?
well now we hv a HUGE table with 550million rows partitioned by year month. how to go from here?

we are using MV for summary data calculation.
Cheers!

[abhaysk]

Originally posted by balajiyes
Hi abhaysk
In some projects we do... we rebuild the fact tables every day aft every upload.
any hint?

U certainly mean Re-org of table than saying it rebuild the table..?

Well Re-Org the table daily, u certainly are joking..

Originally posted by balajiyes
well now we hv a HUGE table with 550million rows partitioned by year month. how to go from here?

U wana Re-Org this table daily as well?
huh?

Well if it one time, then Use Alter Table Move..(I think u can do it partition wise not sure coz never used partitioned tables)

Abhay.

[julian]

Originally posted by jmodic
"When should you rebuild an index" by Jonathan Lewis:
http://www.dbazine.com/jlewis14.shtml

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%.