1. Replication Guide
  2. Improving Replication Performance
  3. Performance Considerations When Altering Tables That Are Replicated

Performance Considerations When Altering Tables That Are Replicated

Altering a table to add or remove columns may lead to performance degradation or poor space utilization.

  • When you alter a table to add one or more columns, the table is allocated a new partition for the additional columns. The additional partition causes extra processing when retrieving the data, resulting in reduced performance. See ALTER TABLE in the Oracle TimesTen In-Memory Database SQL Reference.

  • When you alter a table to drop a column, the space is not always freed resulting in poor space utilization.

Any replication scheme defined with the TABLE DEFINITION CHECKING EXACT attribute requires that the physical structure of the table be identical on both master databases in order to be able to replicate operations between them. When using the EXACT table definition checking attribute, the only method to free the extraneous space resulted from dropped columns or eliminate extra partitions resulting from added columns is to drop and re-create the table, and then reload the data into the table.

However, if you create the tables with the TABLE DEFINITION CHECKING RELAXED attribute, then (while they must have the same key definition, number of columns, and column data types) the physical structure does not need to be identical on both master databases. The TABLE DEFINITION CHECKING RELAXED attribute can result in slightly slower performance, but only if the tables on both masters are not identical. The change in performance depends on the workload and the number of partitions and columns in the tables.

To improve performance for databases set with RELAXED, you can use ttMigrate -r -relaxedUpgrade to coalesce tables eliminating extraneous space from dropped columns or multiple partitions that were created when adding columns. This can be performed on one database, while the other database is still up and accepting requests on behalf of the application. You do not have to take both databases involved in replication down at the same time, but can perform ttMigrate -r -relaxedUpgrade on each one individually one after the other. This is optimal for databases where the tables are altered often and where the database can only perform online upgrades.

You can only coalesce partitions and eliminate extraneous space with ttMigrate -r -relaxedUpgrade on replicated tables when the table definition checking to RELAXED. However, if your tables have been using the EXACT attribute, then you can temporarily set table definition checking to RELAXED, consolidate the partitions and space for your tables, and then reset it to EXACT.

For more information on the TABLE DEFINITION CHECKING RELAXED attribute, see Column Definition Options for Replicated Tables.

Note:

You can check if the table has multiple partitions. For details, see the instructions in both the Understanding Partitions When Using ALTER TABLE section detailed within ALTER TABLE in the Oracle TimesTen In-Memory Database SQL Reference and Check Partition Counts for the Tables in the Oracle TimesTen In-Memory Database Monitoring and Troubleshooting Guide.