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

john_wang — Wed, 19 Jun 2024 11:07:15 GMT

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

Define a View to Expose ROWID
Create a Full Load Only Task
Add the View to "Selected Tables"
Use the ROW_ID Column as the Segment Column
Run the task

Steps

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.
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.
Add the View to "Selected Tables/Views"
Use the ROW_ID Column as the Segment Column
Run the task. The task is now ready to start up.

Environment

all versions
Oracle database all versions

article Qlik Replicate and Oracle source endpoint: use ROWID as segment column in Parallel Load in Official Support Articles

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

Overview

Steps

Environment