CCA Performance: ReIndex CCA Database

Document created by A_H Employee on May 20, 2015Last modified by A_H Employee on May 20, 2015
Version 2Show Document
  • View in full screen mode

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 ##

Attachments

    Outcomes