Replicating nested tables

Replicate supports the replication of Oracle tables containing columns that are nested tables or defined types. To enable this functionality, select the Support nested tables option in the Advanced tab.

Replicate creates the target tables of Oracle nested tables as regular tables without a unique constraint. As you will most likely need join the parent and child tables for meaningful data, it is important to manually create a non-unique index on the NESTED_TABLE_ID column in the target child table. The NESTED_TABLE_ID column can then be used in the JOIN ON clause, together with the parent column corresponding to the child table name. Additionally, creating such an index will improve performance when the target child table data is updated/deleted by Replicate.

It is recommended to configure the task to stop after Full Load completes. After Full Load completes, manually create non-unique indexes for all the replicated child tables on the target, and then resume the task.

If a captured nested table is added to an existing parent table (captured or not captured), Replicate will handle it correctly, but the non-unique index for the corresponding target table will not be created. Note that in this case, if the target child table becomes extremely large, performance may be impacted. In such a case, it is recommended to stop the task, create the index, and then resume the task.

After the nested tables are replicated to the target, the DBA will need to run a JOIN statement on the parent and corresponding child tables in order to flatten the data.

Prerequisites

Make sure that you replicate parent tables for all the replicated nested tables. Both the parent tables (the tables containing the nested table column) and the child (i.e. nested) tables will be available for selection in Replicate.

Supported nested table types

The following nested table types are supported:

• Data type
• User defined Object

Limitations

• Only one level of nesting is supported.
• Replicate does not verify that both the parent and child table(s) are selected for replication. In other words, it's possible to select a parent table without a child and vice versa.

How nested tables are replicated

The parent and nested tables are replicated to the target as follows:

• The parent table is created identical to the source. The nested column will be defined as RAW(16) and contain a reference to its nested tables in the NESTED_TABLE_ID column.

• The child table is created identical to the source, but with an additional column named NESTED_TABLE_ID with the same type as the parent nested column and with the same meaning.

JOIN statement example

To flatten the parent table, the DBA should run a JOIN statement between the parent and child tables, as shown in the following example:

Creating the Type table:

CREATE OR REPLACE TYPE my_tab_t AS TABLE OF VARCHAR2(30);

Creating the parent table with a column of type my_tab_t that was defined above:

CREATE TABLE my_parent_table (id NUMBER PRIMARY KEY, col1 my_tab_t) NESTED TABLE col1 STORE AS col1_tab;

Flattening the my_parent_table:

Select … from my_parent_table parent, col1_tab child where child.nested_table_id = parent.col1