Monday, November 26, 2012

rebuilding indexes to speed up the OSB reporting

Aparently there is a heated debate on whether rebuilding indexes is actually helpful. To me, it worked like a charm.
Before, a simple "select count(*) from WLI_QS_REPORT_ATTRIBUTE;" would take 15 seconds, even if my table was almost empty.
After the rebuild, it was taking milliseconds.



ALTER INDEX IX_WLI_QS_REPORT_ATTRIBUTE_DM REBUILD;
ALTER INDEX IX_WLI_QS_REPORT_ATTRIBUTE_IED REBUILD;


This wiki contrains a lot of valuable advice on how to monitor index usage: http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ

No comments: