Skip to main content

Search

Welcome to Tibero GTS!

How to Transfer Data Using DBLink

Document Type | Technical Information

Category | Migration

Applicable Product Version | 6F

Document Number | TMITI016

 

Overview

This document explains the method of transferring data through DB Link and the data transfer speed according to the method.
Note
This document is a guide based on the following conditions.
  • Virtual Machine: Virtual Box
  • OS: Centos7
  • Tibero: Tibero6
  • Source, Target: Tibero6 (exist on the same server)
 
 

Method

Data Transfer

1. Data Transfer via DBLink (Sourceโ†’Target)

You can transfer data from Source to Target through DB Link.
--Source
SQL> alter session enable parallel dml;
Session altered.

SQL> insert /*+ append parallel(4) */ into tbl_link_test@ttot select /*+ parallel(4) */ * from tbl_link_test;

SQL> select * from v$pe_session; --Check from another session
 SID SERIAL# PEP_ID QCSID QCSERIAL# DEGREE REQ_DEGREE
---------- ---------- ---------- ---------- ---------- ---------- ----------
 97 1601 0 77 531 4 4
 98 1600 0 77 531 4 4
 99 1598 0 77 531 4 4
 100 1599 0 77 531 4 4
4 rows selected.

--Target
SQL> alter table TBL_LINK_TEST parallel 4;
Table 'TBL_LINK_TEST' altered.
SQL> select * from v$pe_session;
0 row selected.
When using DB Link from the Source, the parallel option cannot be used when inserting into the Target table.
However, even if the parallel setting of the Target table is changed in advance before transfer, parallel processing does not occur, so this data transfer method is not recommended.
 

2. Data Transfer via DBLink (Targetโ†’Source)

You can transfer data from Source to Target through DB Link from the Target.
This method allows parallel processing on both Target and Source.
--Target
SQL> alter session enable parallel dml;

Session altered.
SQL> insert /*+ append parallel(4) */ into tbl_link_test select /*+ parallel(4) */ * from tbl_link_test@ttot;

SQL> select * from v$pe_Session; --Check from another session
 SID SERIAL# PEP_ID QCSID QCSERIAL# DEGREE REQ_DEGREE
---------- ---------- ---------- ---------- ---------- ---------- ----------
 97 3879 0 77 3852 4 4
 98 3881 0 77 3852 4 4
 99 3882 0 77 3852 4 4
 100 3884 0 77 3852 4 4
 101 3883 0 77 3852 4 4
 102 3885 0 77 3852 4 4
 103 3880 0 77 3852 4 4
 104 3886 0 77 3852 4 4
8 rows selected.

--Source
SQL> select * from v$pe_session;
 SID SERIAL# PEP_ID QCSID QCSERIAL# DEGREE REQ_DEGREE
---------- ---------- ---------- ---------- ---------- ---------- ----------
 97 4054 0 79 3928 4 4
 98 4053 0 79 3928 4 4
 99 4052 0 79 3928 4 4
 100 4051 0 79 3928 4 4
4 rows selected.
SQL

 

Comparison of Transfer Speeds According to Settings

The test data consists of 10,000,000 rows, approximately 376MB, and the creation script is as follows.
create table tbl_link_test(a number, b varchar2(10), c date);
insert into tbl_link_test select level, 'tbl_link_test', sysdate from dual connect by level <= 10000000;
commit;

create index idx_link_test1 on tbl_link_test(a);
create index idx_link_test2 on tbl_link_test(b);
create index idx_link_test3 on tbl_link_test(c);
alter table tbl_link_test add constraint con_a unique (a);


1. Data Transfer via DBLink (Sourceโ†’Target)

  • All settings default state: Total elapsed time 00:24:30.560933
  • All settings modified state: Total elapsed time 00:14:53.344741
 

2. Data Transfer via DBLink (Targetโ†’Source)

  • All settings default state: Total elapsed time 00:02:35.416980
  • All settings modified state: Total elapsed time 00:00:45.513947
Note
  • Data transferred with nologging cannot be recovered, so it is strongly recommended to take a full backup after the transfer is complete.
  • When transferring data via DB Link, you cannot query only specific partitions. Therefore, the operator must specify range conditions separately when querying.
  • In a TAC environment, shut down one node before proceeding with data transfer. When transferring data per user, transfer data with different users on each node.

 

 

 

Related to