Clarity Configuration Automation

CCA Performance: ReIndex CCA Database 

May 20, 2015 08:17 AM

Certain operations taking too long? Try re-indexing all the tables in the database

 

For SQL Server::

 

USE ccadb
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO

 

For Oracle:

select 'ALTER INDEX '||INDEX_NAME||' REBUILD' 
from cca_indexes
where owner=upper('cca');

 

 

How long will this take?

For SQL Server, a 100gig CCA database file took 3 minutes, but a 500gig took 8 hours; it does not go based on the size of the database, but more on the amount of rows and number of foreign keys

For Oracle, no time data has been captured

 

Results:

A Report that took 3 hours to run now runs in 2 minutes

Random jobs from 1 hour to 30seconds

 

## Backup Database performing this operation ##

Statistics
0 Favorited
5 Views
0 Files
0 Shares
0 Downloads

Related Entries and Links

No Related Resource entered.