Qlik Replicate and Oracle source endpoint: use ROWID as segment column in Parallel Load

[john_wang]

Parallel Load is often used to accelerate the replication of large tables by splitting the table into segments. Primary Key (or index) columns are not mandatory for Parallel Load; any column(s) can be used as a segment column as long as it can divide the data into segments. However, ROWID can sometimes be more efficient if the table has no Primary Key, no other indexes, and no other columns that can easily split the table rows.

Currently, Oracle data types ROWID/UROWID are not supported in the major versions of Qlik Replicate. As a result, columns with these data types will not be visible in the Qlik Replicate GUI, making it impossible to utilize ROWID directly in the Parallel Load design window.

This article provides a guide on how to utilize ROWID in Parallel Load.

Overview

1. Define a View to Expose ROWID
2. Create a Full Load Only Task
3. Add the View to "Selected Tables"
4. Use the ROW_ID Column as the Segment Column
5. Run the task

Steps

1. Define a View to Expose ROWID
   
   

   CREATE VIEW scott.kitv1000rowid AS
   SELECT rowid||'' AS row_id, id, name, notes
   FROM scott.kitnopk1000
   ORDER BY row_id;

   
   In this example, the expression rowid||'' implicitly converts the ROWID data type to CHAR, allowing the column ROW_ID (maps to ROWID) to be visible in the Qlik Replicate GUI. The ORDER BY statement ensures that the rows are split accurately.
   
   
2. Create a Full Load Only Task
   
   Note that the view is only visible in a Full Load Only task. If Change Processing is enabled, it is not possible to add views to the monitor tables list.
   
3. Add the View to "Selected Tables/Views"
   
   
4. Use the ROW_ID Column as the Segment Column
   
   
5. Run the task. The task is now ready to start up.

Environment

• Qlik Replicate all versions
• Oracle database all versions