Schema Changes and Big Tables

The schema manager utilitywith ClarifyCRM 12.5 drops and re-creates indexes on all clarify tables during a schema upgrade. If there are big tables with millions of records, the schema upgrade process may take for ever.

Here is a smart way to workaround this:

1. Rename table_big to table_big_temp.

2. Create table_big with 50 records (say) as below

create table_big as select * from table_big_temp where rownum <=50

3. Apply the schema changes using schema manager utility.

4.  Rename table_big_temp to table_big

For step 3 above, if there are schema changes (create, drop, modify) for table_big itself, make those changes manually on table_big_temp using alter command and populate any new columns if needed.

This would greatly reduce the time taken by schema manager utility to apply the schema. Remember to repeat the steps above for any relational tables. For example, if table_site is big, please be sure to perform the steps on related table_address as well.