Snapshot Replication v6.2
In snapshot replication, the target tables are completely reloaded from the source tables. The database system’s truncate operation is used to delete all rows from the target tables.
For Oracle and SQL Server only: Oracle and SQL Server target tables are loaded using JDBC batches of INSERT statements.
For Postgres only: In general, Postgres target tables are loaded using the JDBC COPY command since using truncation and COPY is generally faster than if you were to execute an SQL DELETE statement against the entire table and then add the rows using JDBC batches of INSERT statements. If the COPY command fails, the publication server retries the snapshot using JDBC batches of INSERT statements.
If the target table (regardless of database type) contains a large object data type such as BYTEA, BLOB, or CLOB then rows are loaded one at a time per batch using an INSERT statement. This is to avoid a heap space error resulting from potentially large rows. Loading time can be decreased by allowing multiple inserts per batch, which is done by adjusting the configuration option lobBatchSize described in Optimizing Snapshot Replication.
Note
Advanced Server supports a number of aliases for data types. Such aliases that translate to BYTEA are treated as large object data types. See the Database Compatibility for Oracle Developers Reference Guide for a listing of Advanced Server data types. (See the Database Compatibility for Oracle Developer’s Guide for Advanced Server version 9.5 or earlier versions.)
Under certain circumstances, the corresponding Postgres target table created for certain types of Oracle partitioned tables is a set of inherited tables. In these cases, the SQL DELETE statement is used on the inherited child tables instead of truncation. See Replicating Oracle Partitioned Tables for additional information on replicating Oracle partitioned tables.
A server configuration option is available that forces the snapshot replication process to use the Oracle database link utility instead of JDBC COPY to populate the Postgres target tables from an Oracle publication. Oracle database link provides an additional performance improvement over JDBC COPY. See Optimizing Snapshot Replication for information on using the Oracle database link option.
See Optimizing Snapshot Replication for information on various configuration options to optimize snapshot replication.