全量旁路导入

本文介绍如何通过 LOAD DATA 语句、INSERT INTO SELECT 语句和 CREATE TABLE AS SELECT 语句实现全量旁路导入。

注意事项

在使用全量旁路导入时，有以下注意事项：

OceanBase 数据库从 V4.3.0 版本开始支持 LOB 数据类型的旁路导入。
旁路导入期间会加表锁，表无法被写入其他数据，整个过程中表是只读的。
全量旁路导入适用于大表首次导入、10 GB~TB 级别的数据迁移以及 CPU 和内存都不是特别充裕的场景，因为旁路导入的执行路径短，可以省 CPU 开销。
LOAD DATA 语句支持在多行事务中运行，在执行时会主动提交之前的事务。
使用 INSERT INTO SELECT 语句旁路导入数据时，只支持 PDML（Parallel Data Manipulation Language，并行数据操纵语言），非 PDML 不能用旁路导入。
在 LOAD DATA 语句和 INSERT INTO SELECT 语句中使用指定分区旁路导入时需要注意，目标表不能是复制表，且不能含有自增列、标识列、Global Index。
对于 V4.3.5 版本，从 V4.3.5 BP1 版本开始全量旁路导入有以下变更：
- 如果目标表最后一级分区为 Hash/Key 分区，支持分区级旁路导入。
- 当 Session 级变量 foreign_key_checks 取值为 False 时，旁路导入操作不会进行外键约束校验。
LOAD DATA 语句对于带唯一索引的全量旁路导入，当出现重复唯一索引键时，暂时不支持 REPLACE 或 IGNORE 关键字，也不支持设置容错行。

使用 LOAD DATA 语句旁路导入数据

LOAD DATA 语句通过使用 append/direct() Hint 来执行旁路导入，在未指定 Hint 时能基于配置项 default_load_mode 确定导入数据的行为。

注意

OceanBase 数据库通过并行处理技术优化 LOAD DATA 的数据导入速率。该操作将数据分成多个子任务并行执行，每个子任务都视为独立的事务，执行顺序是不固定的。因此对于没有主键的表，数据写入顺序可能与原文件中的顺序不同。

使用限制

在导入过程中无法同时执行两个写操作语句（即不能同时写一个表），因为导入过程中会先加表锁，并且整个导入过程中只能进行读操作。
不支持在触发器（Trigger）使用。
不支持含有生成列的表（某些索引会产生隐藏生成列，例如 KEY idx_c2 (c2(16)) GLOBAL）。
不支持单行超过 2M 的数据导入。
不支持 Liboblog 和闪回查询（Flashback Query）。

使用语法

LOAD DATA /*+ [APPEND | DIRECT(need_sort,max_error,'full')] parallel(N) */ [REMOTE_OSS | LOCAL] INFILE 'file_name' INTO TABLE table_name [PARTITION(PARTITION_OPTION)] [COMPRESSION]...

更多 LOAD DATA 语法的信息，请参见 LOAD DATA。

参数解释：

参数	描述
APPEND \| DIRECT()	使用 Hint 启用旁路导入功能。 `APPEND` Hint 默认等同于使用的 `DIRECT(true, 0)`，同时可以实现在线收集统计信息（`GATHER_OPTIMIZER_STATISTICS` Hint）的功能。 `direct()` 参数解释如下： `need_sort`：表示写入的数据是否需要排序，值为 `bool` 类型： `true`：表示需要排序。 `false`：表示不需要排序。说明在有序文件和备份数据（已按主键排序）导入的场景中，可设置 `need_sort` 的取值为 `false` 跳过排序流程；若数据未按主键排序且未显式开启排序（`need_sort` 的取值为 `true`）时，会触发错误。 `max_error`：表示最大容忍的错误行数。值为 `INT` 类型，超过这个数值导入任务执行会失败。 `full`：表示全量旁路导入，可选项，取值须使用英文单引号包起来。
parallel(N)	必填项，加载数据的并行度，默认为 4。
REMOTE_OSS \| LOCAL	可选项， `REMOTE_OSS` 用于指定是否从对象存储上读取数据。目前支持的对象存储包括阿里云 OSS、AWS S3 以及兼容 S3 的对象存储，如 OBS、GCS、COS 等。 `LOCAL` 用于指定是否从客户端的本地文件系统读取数据。如果不使用 `REMOTE_OSS` 和 `LOCAL`，那么将从服务器端（OBServer 节点）的文件系统读取数据。
file_name	指定输入文件的路径和文件名。该参数格式与 `REMOTE_OSS \| LOCAL` 的类型相互对应：当指定 `REMOTE_OSS` 时，`file_name` 表示导入文件在对象存储上，常见支持的对象存储格式如下：阿里云 OSS：`LOAD DATA /+ [APPEND \| DIRECT(need_sort,max_error)] parallel(N) / REMOTE_OSS INFILE 'oss://$PATH/$FILENAME/?host=$HOST&access_id=$ACCESS_ID&access_key=$ACCESS_KEY' INTO TABLE table_name ...;` AWS S3：`LOAD DATA /+ [APPEND \| DIRECT(need_sort,max_error)] parallel(N) / REMOTE_OSS INFILE 's3://$PATH/$FILENAME/?host=$HOST&access_id=$ACCESS_ID&access_key=$ACCESS_KEY&s3_region=S3_REGION' INTO TABLE table_name ...;` 兼容 S3 的对象存储：`LOAD DATA /+ [APPEND \| DIRECT(need_sort,max_error)] parallel(N) / REMOTE_OSS INFILE 's3://$PATH/$FILENAME/?host=$HOST&access_id=$ACCESS_ID&access_key=$ACCESS_KEY' INTO TABLE table_name ...;` 当指定 `LOCAL` 时，表示导入文件在客户端上，此时语法格式为 `LOAD DATA /+ [APPEND \| DIRECT(need_sort,max_error)] parallel(N) / LOCAL INFILE '/$PATH/$FILENAME' INTO TABLE table_name ...;`。当 `REMOTE_OSS` 和 `LOCAL` 均不指定时，表示导入文件在 OBServer 节点上，此时语法格式同仅指定 `LOCAL` 时一样。对于上述各参数的解释如下： `$PATH`：指定了存储桶中的文件路径，表示文件所在的目录。 `$FILENAME`：指定了文件的名称，表示要访问的具体文件。 `$HOST`：指定了 OSS 服务的主机名或 CDN 加速的域名，即要访问的 OSS 服务的地址。 `$ACCESS_ID`：指定了访问 OSS 服务所需的 Access Key ID，用于身份验证。 `$ACCESSKEY`：指定了访问 OSS 服务所需的 Access Key Secret，用于身份验证。 `S3_REGION`：指定了 Amazon S3 存储桶所在的区域。对象存储为 AWS S3 时，必须指定的参数。说明在导入 OSS 上的文件时，需要确保以下信息：确保访问 OSS 存储桶和文件的权限。你需要拥有足够的权限来读取指定的存储桶和文件。这通常需要在 OSS 控制台或通过 OSS API 设置访问权限，并将访问密钥（Access Key ID 和 Access Key Secret）配置为具有适当权限的凭据。确保数据库服务器可以通过网络连接到指定的 `$HOST` 地址，以访问 OSS 服务。如果使用的是 OSS 服务的 CDN 加速域名，还需要确保 CDN 配置正确，并且网络连接正常。
table_name	导入数据的表的名称，支持指定表任意列数目。
PARTITION_OPTION	指定分区旁路导入时的分区名： partition_option_list：一级分区列表，同时插入多个分区时以逗号（,）分隔。 subpartition_option_list：二级分区列表，同时插入多个分区时以逗号（,）分隔。
COMPRESSION	指定压缩文件格式，取值有 `AUTO`：根据文件名后缀自动探测压缩算法。在使用 `AUTO` 参数时，不同的后缀名有对应的压缩格式。 `.gz`：对应 GZIP 压缩文件。 `.deflate`：对应 DEFLATE 压缩文件。 `.zst/.zstd`：对应 ZSTD 压缩文件。 `NONE`：表示文件没有压缩。 `GZIP`：GZIP 压缩文件。 `DEFLATE`：不带元数据的GZIP压缩文件。 `ZSTD`：ZSTD 压缩文件。可以明确文件的压缩格式或者让程序根据文件名后缀来探测压缩格式。

使用示例

说明

下面的示例是从服务器端文件导入数据的方法。OceanBase 数据库 LOAD DATA 语句旁路导入数据还支持加载本地文件（LOCAL INFILE）的方式。更多有关 LOAD DATA LOCAL INFILE 的示例信息，请参见使用 LOAD DATA 语句导入数据

MySQL 模式

Oracle 模式

登录到需要连接 OBServer 节点所在的机器上，在 /home/admin 目录下创建测试数据。

说明

OceanBase 数据库中的 LOAD DATA 语句仅支持加载 OBServer 节点本地的输入文件。因此，需要在导入之前将文件拷贝到某台 OBServer 上。
1. 进入 OBServer 节点所在的机器。
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. 创建测试数据 tbl1.csv。
```
[admin@xxx /home/admin]# vi tbl1.csv
1,11
2,22
3,33
```
设置导入的文件路径。

设置系统变量 secure_file_priv，配置导入或导出文件时可以访问的路径。

注意

由于安全原因，设置系统变量 secure_file_priv 时，只能通过本地 Unix Socket 连接数据库执行修改该全局变量的 SQL 语句。更多信息，请参见 secure_file_priv。
1. 登录到需要连接 OBServer 节点所在的机器上。
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. 执行以下命令，通过本地 Unix Socket 连接方式连接租户 mysql001。
```
obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
```
3. 设置导入路径为 /home/admin。
```
obclient [(none)]> SET GLOBAL secure_file_priv = "/home/admin";
Query OK, 0 rows affected
```

重新连接数据库后，使用 LOAD /*+ DIRECT */ DATA 语句导入数据。

创建表 tbl2。

obclient [test]> CREATE TABLE tbl2 (
                    col1 INT PRIMARY KEY,
                    col2 INT
                 )
                 PARTITION BY RANGE (col1)
                    SUBPARTITION BY RANGE (col1) (
                       PARTITION p0 VALUES LESS THAN (100) (
                             SUBPARTITION p0_1 VALUES LESS THAN (50),
                             SUBPARTITION p0_2 VALUES LESS THAN (100)
                       ),                           
                       PARTITION p1 VALUES LESS THAN (200) (
                             SUBPARTITION p1_1 VALUES LESS THAN (150),
                             SUBPARTITION p1_2 VALUES LESS THAN (200)
                       )
                 );
Query OK, 0 rows affected

查询表 tbl2 是否有数据，此时显示表为空。
```
obclient [test]> SELECT * FROM tbl2;
Empty set
```

使用旁路导入将 tbl1.csv 文件中的数据导入到表 tbl2。

指定表 tbl2 的所有列导入数据。

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

指定表 tbl2 的指定任意列导入数据。例如指定 col1，col2 列。

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ','(col1,col2);
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

指定表 tbl2 的一级分区导入数据。

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 partition(p0, p1)  FIELDS TERMINATED BY ',';

指定表 tbl2 的二级分区导入数据。

obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 partition(p0sp0_1, p1sp1_1)  FIELDS TERMINATED BY ',';

使用配置项 default_load_mode 导入数据。

设置 default_load_mode 的值为 FULL_DIRECT_WRITE。

obclient [test]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

不指定 LOAD DATA 语句的 Hint。

obclient [test]> LOAD DATA INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';

验证表 tbl2 中是否已导入数据。

obclient [test]>  SELECT * FROM tbl2;

查询结果如下：

 +------+------+
 | col1 | col2 |
 +------+------+
 |    1 |   11 |
 |    2 |   22 |
 |    3 |   33 |
 +------+------+
 3 rows in set

结果显示表 tbl2 中已导入数据。

登录到需要连接 OBServer 节点所在的机器上，在 /home/admin 目录下创建测试数据 tbl1。

说明

OceanBase 数据库中的 LOAD DATA 语句仅支持加载 OBServer 节点本地的输入文件。因此，需要在导入之前将文件拷贝到某台 OBServer 上。
1. 进入 OBServer 节点所在的机器。
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. 创建测试数据 tbl1.csv。
```
[admin@xxx /home/admin]# vi tbl1.csv
1,11
2,22
3,33
```
设置导入的文件路径。

设置系统变量 secure_file_priv，配置导入或导出文件时可以访问的路径。

注意

由于安全原因，设置系统变量 secure_file_priv 时，只能通过本地 Unix Socket 连接数据库执行修改该全局变量的 SQL 语句。更多信息，请参见 secure_file_priv。
1. 登录到需要连接 OBServer 节点所在的机器上。
```
[xxx@xxx /home/admin]# ssh admin@10.10.10.1
```
2. 执行以下命令，通过本地 Unix Socket 连接方式连接租户 oracle001。
```
obclient -S /home/admin/oceanbase/run/sql.sock -usys@oracle001 -p******
```
3. 设置导入路径为 /home/admin。
```
obclient [(none)]> SET GLOBAL secure_file_priv = "/home/admin";
Query OK, 0 rows affected
```

重新连接数据库后，使用 LOAD /*+ DIRECT */ DATA 语句导入数据。

创建表 tbl2。

obclient [SYS]> CREATE TABLE tbl2 (
                    col1 INT PRIMARY KEY,
                    col2 INT
                 )
                 PARTITION BY RANGE (col1)
                    SUBPARTITION BY RANGE (col1) (
                       PARTITION p0 VALUES LESS THAN (100) (
                             SUBPARTITION p0_1 VALUES LESS THAN (50),
                             SUBPARTITION p0_2 VALUES LESS THAN (100)
                       ),
                       PARTITION p1 VALUES LESS THAN (200) (
                             SUBPARTITION p1_1 VALUES LESS THAN (150),
                             SUBPARTITION p1_2 VALUES LESS THAN (200)
                       )
                 );
Query OK, 0 rows affected

查询表 tbl2 是否有数据，此时显示表为空。
```
obclient [SYS]> SELECT * FROM tbl2;
Empty set
```

使用旁路导入将 tbl1.csv 文件中的数据导入到表 tbl2。

指定表 tbl2 的所有列导入数据。

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

指定表 tbl2 的指定任意列导入数据。例如指定 col1，col2 列。

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ','(col1,col2);
Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

（可选）指定表 tbl2 的一级分区导入数据。

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 partition(p0, p1)  FIELDS TERMINATED BY ',';

（可选）指定表 tbl2 的二级分区导入数据。

obclient [SYS]> LOAD DATA /*+ direct(true,1024,'full') parallel(16) */ INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 partition(p0sp0_1, p1sp1_1)  FIELDS TERMINATED BY ',';

使用配置项 default_load_mode 导入数据。

设置 default_load_mode 的值为 FULL_DIRECT_WRITE。

obclient [SYS]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

不指定 LOAD DATA 语句的 Hint。

obclient [SYS]> LOAD DATA INFILE '/home/admin/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';

验证表 tbl2 中是否已导入数据。

obclient [SYS]>  SELECT * FROM tbl2;

查询结果如下：

 +------+------+
 | col1 | col2 |
 +------+------+
 |    1 |   11 |
 |    2 |   22 |
 |    3 |   33 |
 +------+------+
 3 rows in set

结果显示表 tbl2 中已导入数据。

使用 INSERT INTO SELECT 语句旁路导入数据

INSERT INTO SELECT 语句通过 Hint 使用 append/direct() 加上 enable_parallel_dml 来执行旁路导入，在未指定 Hint 时能基于配置项 default_load_mode 确定导入数据的行为。

使用限制

只支持 PDML（Parallel Data Manipulation Language，并行数据操纵语言），非 PDML 不能用旁路导入。有关并行 DML 的更多信息，参见并行 DML。
在导入过程中无法同时执行两个写操作语句（即不能同时写一个表），因为导入过程中会先加表锁，并且整个导入过程中只能进行读操作。
旁路导入属于 DDL 语句，无法在多行事务（包含多个操作的事务）中执行。
- 不能在 Begin 中执行。
- Autocommit 必须设置为 1。
不支持在触发器（Trigger）使用。
不支持含有生成列的表（某些索引会产生隐藏生成列，例如 KEY idx_c2 (c2(16)) GLOBAL）。
不支持 Liboblog 和闪回查询（Flashback Query）。

使用语法

INSERT /*+ [APPEND |DIRECT(need_sort,max_error,'full')] enable_parallel_dml parallel(N) */ INTO  table_name [PARTITION(PARTITION_OPTION)] select_sentence

更多 INSERT INTO 语法的信息，请参见 INSERT（MySQL 模式）和 INSERT（Oracle 模式）。

参数解释：

参数	描述
APPEND \| DIRECT()	使用 Hint 启用旁路导入功能。 `APPEND` Hint 默认等同于使用的 `DIRECT(true, 0)`，同时可以实现在线收集统计信息（`GATHER_OPTIMIZER_STATISTICS` Hint）的功能。 `DIRECT()` 参数解释如下： `need_sort`：表示写入的数据是否需要排序，值为 `bool` 类型： `true`：表示需要排序。 `false`：表示不需要排序。 `max_error`：表示最大容忍的错误行数。值为 `INT` 类型，超过这个数值导入任务执行会失败。 `full`：表示全量旁路导入，可选项，取值须使用英文单引号包起来。
enable_parallel_dml	加载数据的并行度。说明一般情况下，`enable_parallel_dml` Hint 和 `parallel` Hint 必须配合使用才能开启并行 DML。不过，当目标表的 Schema 上指定了表级别的并行度时，仅需指定 `enable_parallel_dml` Hint。
parallel(N)	加载数据的并行度，必填项，取值是大于 1 的整数。
table_name	导入数据的表的名称，支持指定表任意列数目。
PARTITION_OPTION	指定分区旁路导入时的分区名： partition_option_list：一级分区列表，同时插入多个分区时以逗号（,）分隔。 subpartition_option_list：二级分区列表，同时插入多个分区时以逗号（,）分隔。

使用示例

MySQL 模式

Oracle 模式

示例 1：使用 INSERT INTO SELECT 全量旁路导入的完整示例。

使用旁路导入将表 tbl2 中的部分数据导入到 tbl1 中。

查询表 tbl1 是否有数据，此时显示表为空。
```
obclient [test]> SELECT * FROM tbl1;
Empty set
```

查询表 tbl2 是否有数据。

obclient [test]> SELECT * FROM tbl2;

查询到表 tbl2 有数据。

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 | a1   |   11 |
|    2 | a2   |   22 |
|    3 | a3   |   33 |
+------+------+------+
3 rows in set

使用旁路导入将表 tbl2 中的数据导入到表 tbl1。

指定 INSERT INTO SELECT 语句的 Hint。

不指定分区导入。

obclient [test]> INSERT /*+ DIRECT(true, 0, 'full') enable_parallel_dml parallel(16) */ INTO tbl1 SELECT t2.col1,t2.col3 FROM tbl2 t2;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

（可选）指定分区导入。

obclient [test]> INSERT /*+ DIRECT(true, 0, 'full') enable_parallel_dml parallel(16) */ INTO tbl1 partition(p0, p1) SELECT t2.col1,1 FROM tbl2 partition(p0, p1) t2;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient [test]> INSERT /*+ DIRECT(true, 0, 'full') enable_parallel_dml parallel(16) */ INTO tbl1 partition(p0, p1) SELECT t2.col1,1 FROM tbl2 partition(p0sp0_1, p1sp1_1) t2;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

不指定 INSERT INTO SELECT 语句的 Hint。

设置配置项 default_load_mode 的值为 FULL_DIRECT_WRITE。

obclient [test]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

obclient [test]> INSERT INTO tbl1 SELECT t2.col1,t2.col3 FROM tbl1 t2;

验证表 tbl1 中是否已导入数据。

obclient [test]> SELECT * FROM tbl1;

查询结果如下：

+------+------+
| col1 | col2 |
+------+------+
|    1 |   11 |
|    2 |   22 |
|    3 |   33 |
+------+------+
3 rows in set

结果显示表 tbl1 中已导入数据。

(可选)在 EXPLAIN EXTENDED 语句的返回结果的 Note 中，查看是否通过旁路导入写入的数据。

obclient [test]> EXPLAIN EXTENDED INSERT /*+ direct(true, 0, 'full') enable_parallel_dml parallel(16) */ INTO tbl1 SELECT t2.col1,t2.col3 FROM tbl2 t2;

返回结果如下：

+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                   |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ==============================================================================                                                                               |
| |ID|OPERATOR                           |NAME           |EST.ROWS|EST.TIME(us)|                                                                               |
| ------------------------------------------------------------------------------                                                                               |
| |0 |PX COORDINATOR                     |               |3       |27          |                                                                               |
| |1 |└─EXCHANGE OUT DISTR               |:EX10001       |3       |27          |                                                                               |
| |2 |  └─INSERT                         |               |3       |26          |                                                                               |
| |3 |    └─EXCHANGE IN DISTR            |               |3       |1           |                                                                               |
| |4 |      └─EXCHANGE OUT DISTR (RANDOM)|:EX10000       |3       |1           |                                                                               |
| |5 |        └─SUBPLAN SCAN             |ANONYMOUS_VIEW1|3       |1           |                                                                               |
| |6 |          └─PX BLOCK ITERATOR      |               |3       |1           |                                                                               |
| |7 |            └─TABLE FULL SCAN      |t2             |3       |1           |                                                                               |
| ==============================================================================                                                                               |
| Outputs & filters:                                                                                                                                           |
| -------------------------------------                                                                                                                        |
|   0 - output(nil), filter(nil), rowset=16                                                                                                                    |
|   1 - output(nil), filter(nil), rowset=16                                                                                                                    |
|       dop=16                                                                                                                                                 |
|   2 - output(nil), filter(nil)                                                                                                                               |
|       columns([{tbl1: ({tbl1: (tbl1.__pk_increment(0x7efa63627790), tbl1.col1(0x7efa63611980), tbl1.col3(0x7efa63611dc0))})}]), partitions(p0),              |
|       column_values([T_HIDDEN_PK(0x7efa63627bd0)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.col1(0x7efa63626f10))(0x7efa63627ff0)], [column_conv(INT, |
|       PS:(11,0),NULL,ANONYMOUS_VIEW1.col3(0x7efa63627350))(0x7efa6362ff20)])                                                                                 |
|   3 - output([T_HIDDEN_PK(0x7efa63627bd0)], [ANONYMOUS_VIEW1.col1(0x7efa63626f10)], [ANONYMOUS_VIEW1.col3(0x7efa63627350)]), filter(nil), rowset=16          |
|   4 - output([T_HIDDEN_PK(0x7efa63627bd0)], [ANONYMOUS_VIEW1.col1(0x7efa63626f10)], [ANONYMOUS_VIEW1.col3(0x7efa63627350)]), filter(nil), rowset=16          |
|       dop=16                                                                                                                                                 |
|   5 - output([ANONYMOUS_VIEW1.col1(0x7efa63626f10)], [ANONYMOUS_VIEW1.col3(0x7efa63627350)]), filter(nil), rowset=16                                         |
|       access([ANONYMOUS_VIEW1.col1(0x7efa63626f10)], [ANONYMOUS_VIEW1.col3(0x7efa63627350)])                                                                 |
|   6 - output([t2.col1(0x7efa63625ed0)], [t2.col3(0x7efa63626780)]), filter(nil), rowset=16                                                                   |
|   7 - output([t2.col1(0x7efa63625ed0)], [t2.col3(0x7efa63626780)]), filter(nil), rowset=16                                                                   |
|       access([t2.col1(0x7efa63625ed0)], [t2.col3(0x7efa63626780)]), partitions(p0)                                                                           |
|       is_index_back=false, is_global_index=false,                                                                                                            |
|       range_key([t2.__pk_increment(0x7efa63656410)]), range(MIN ; MAX)always true                                                                            |
| Used Hint:                                                                                                                                                   |
| -------------------------------------                                                                                                                        |
|   /*+                                                                                                                                                        |
|                                                                                                                                                              |
|       USE_PLAN_CACHE( NONE )                                                                                                                                 |
|       PARALLEL(16)                                                                                                                                           |
|       ENABLE_PARALLEL_DML                                                                                                                                    |
|       DIRECT(TRUE, 0, 'FULL')                                                                                                                                |
|   */                                                                                                                                                         |
| Qb name trace:                                                                                                                                               |
| -------------------------------------                                                                                                                        |
|   stmt_id:0, stmt_type:T_EXPLAIN                                                                                                                             |
|   stmt_id:1, INS$1                                                                                                                                           |
|   stmt_id:2, SEL$1                                                                                                                                           |
| Outline Data:                                                                                                                                                |
| -------------------------------------                                                                                                                        |
|   /*+                                                                                                                                                        |
|       BEGIN_OUTLINE_DATA                                                                                                                                     |
|       PARALLEL(@"SEL$1" "t2"@"SEL$1" 16)                                                                                                                     |
|       FULL(@"SEL$1" "t2"@"SEL$1")                                                                                                                            |
|       USE_PLAN_CACHE( NONE )                                                                                                                                 |
|       PARALLEL(16)                                                                                                                                           |
|       ENABLE_PARALLEL_DML                                                                                                                                    |
|       OPTIMIZER_FEATURES_ENABLE('4.3.3.0')                                                                                                                   |
|       DIRECT(TRUE, 0, 'FULL')                                                                                                                                |
|       END_OUTLINE_DATA                                                                                                                                       |
|   */                                                                                                                                                         |
| Optimization Info:                                                                                                                                           |
| -------------------------------------                                                                                                                        |
|   t2:                                                                                                                                                        |
|       table_rows:3                                                                                                                                           |
|       physical_range_rows:3                                                                                                                                  |
|       logical_range_rows:3                                                                                                                                   |
|       index_back_rows:0                                                                                                                                      |
|       output_rows:3                                                                                                                                          |
|       table_dop:16                                                                                                                                           |
|       dop_method:Global DOP                                                                                                                                  |
|       avaiable_index_name:[tbl2]                                                                                                                             |
|       stats info:[version=0, is_locked=0, is_expired=0]                                                                                                      |
|       dynamic sampling level:0                                                                                                                               |
|       estimation method:[DEFAULT, STORAGE]                                                                                                                   |
|   Plan Type:                                                                                                                                                 |
|       DISTRIBUTED                                                                                                                                            |
|   Note:                                                                                                                                                      |
|       Degree of Parallelism is 16 because of hint                                                                                                            |
|       Direct-mode is enabled in insert into select                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
77 rows in set (0.009 sec)

示例 2：对于 Range-Hash 二级分区表，指定一级分区旁路导入数据。

目标表为二级分区，一级为 Range 分区，二级为 Hash 分区，指定一级分区全量旁路导入。

创建包含两个分区的表 tbl1，一级为 Range 分区，二级为 Hash 分区。

obclient [test]> CREATE TABLE tbl1(col1 INT,col2 INT)
                  PARTITION BY RANGE COLUMNS(col1)
                     SUBPARTITION BY HASH(col2) SUBPARTITIONS 3 (
                        PARTITION p0 VALUES LESS THAN(10),                                               
                        PARTITION p1 VALUES LESS THAN(20));

使用旁路导入将表 tbl2 中的数据导入到表 tbl1 的 p0、p1 分区中。

obclient [test]> insert /*+ direct(true, 0, 'full') enable_parallel_dml parallel(3) append */ into tbl1 partition(p0,p1) select * from tbl2 where col1 <20;

示例 1：使用 INSERT INTO SELECT 全量旁路导入的完整示例。

使用旁路导入将表 tbl4 中的部分数据导入到 tbl3 中。

查询表 tbl3 是否有数据，此时显示表为空。
```
obclient [test]> SELECT * FROM tbl3;
Empty set
```

查询表 tbl4 是否有数据。

obclient [test]> SELECT * FROM tbl4;

查询到表 tbl4 有数据。

+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
|    1 | a1   |   11 |
|    2 | a2   |   22 |
|    3 | a3   |   33 |
+------+------+------+
3 rows in set (0.000 sec)

使用旁路导入将表 tbl4 中的数据导入到表 tbl3。

指定 INSERT INTO SELECT 语句的 Hint。

不指定分区导入。

obclient [test]> INSERT /*+ direct(true, 0, 'full') enable_parallel_dml parallel(16) */ INTO tbl3 SELECT t2.col1, t2.col3 FROM tbl4 t2 WHERE ROWNUM <= 10000;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

（可选）指定分区导入。

obclient [test]> INSERT /*+ DIRECT(true, 0, 'full') enable_parallel_dml parallel(16) */ INTO tbl3 partition(p0, p1) SELECT t2.col1,1 FROM tbl4 partition(p0, p1) t2;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient [test]> INSERT /*+ DIRECT(true, 0, 'full') enable_parallel_dml parallel(16) */ INTO tbl3 partition(p0, p1) SELECT t2.col1,1 FROM tbl4 partition(p0sp0_1, p1sp1_1) t2;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

不指定 INSERT INTO SELECT 语句的 Hint。

设置配置项 default_load_mode 的值为 FULL_DIRECT_WRITE。

obclient [test]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

obclient [test]> INSERT INTO tbl3 SELECT t2.col1, t2.col3 FROM tbl4 t2 WHERE ROWNUM <= 10000;

验证表 tbl3 中是否已导入数据。

obclient [test]> SELECT * FROM tbl3;

查询结果如下：

+------+------+
| col1 | col3 |
+------+------+
|    1 |   11 |
|    2 |   22 |
|    3 |   33 |
+------+------+
3 rows in set

结果显示表 tbl3 中已导入数据。

(可选)在 EXPLAIN EXTENDED 语句的返回结果的 Note 中，查看是否通过旁路导入写入的数据。

obclient [test]> EXPLAIN EXTENDED INSERT /*+ direct(true, 0, 'full') enable_parallel_dml parallel(16) */ INTO tbl3 SELECT t2.col1,t2.col3 FROM tbl4 t2;

返回结果如下：

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ===================================================================================                                                                                                                                        |
| |ID|OPERATOR                                |NAME           |EST.ROWS|EST.TIME(us)|                                                                                                                                        |
| -----------------------------------------------------------------------------------                                                                                                                                        |
| |0 |OPTIMIZER STATS MERGE                   |               |3       |29          |                                                                                                                                        |
| |1 |└─PX COORDINATOR                        |               |3       |29          |                                                                                                                                        |
| |2 |  └─EXCHANGE OUT DISTR                  |:EX10001       |3       |27          |                                                                                                                                        |
| |3 |    └─INSERT                            |               |3       |27          |                                                                                                                                        |
| |4 |      └─OPTIMIZER STATS GATHER          |               |3       |1           |                                                                                                                                        |
| |5 |        └─EXCHANGE IN DISTR             |               |3       |1           |                                                                                                                                        |
| |6 |          └─EXCHANGE OUT DISTR (RANDOM) |:EX10000       |3       |1           |                                                                                                                                        |
| |7 |            └─SUBPLAN SCAN              |ANONYMOUS_VIEW1|3       |1           |                                                                                                                                        |
| |8 |              └─PX BLOCK ITERATOR       |               |3       |1           |                                                                                                                                        |
| |9 |                └─COLUMN TABLE FULL SCAN|T2             |3       |1           |                                                                                                                                        |
| ===================================================================================                                                                                                                                        |
| Outputs & filters:                                                                                                                                                                                                         |
| -------------------------------------                                                                                                                                                                                      |
|   0 - output(nil), filter(nil), rowset=16                                                                                                                                                                                  |
|   1 - output([column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL1(0x7ef8f6027720))(0x7ef8f60287f0)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60))(0x7ef8f6030710)]), filter(nil), rowset=16 |
|   2 - output([column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL1(0x7ef8f6027720))(0x7ef8f60287f0)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60))(0x7ef8f6030710)]), filter(nil), rowset=16 |
|       dop=16                                                                                                                                                                                                               |
|   3 - output([column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL1(0x7ef8f6027720))(0x7ef8f60287f0)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60))(0x7ef8f6030710)]), filter(nil)            |
|       columns([{TBL3: ({TBL3: (TBL3.__pk_increment(0x7ef8f6027fa0), TBL3.COL1(0x7ef8f6011d50), TBL3.COL3(0x7ef8f6012190))})}]), partitions(p0),                                                                            |
|       column_values([T_HIDDEN_PK(0x7ef8f60283e0)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL1(0x7ef8f6027720))(0x7ef8f60287f0)], [column_conv(NUMBER,                                                         |
|       PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60))(0x7ef8f6030710)])                                                                                                                                               |
|   4 - output([column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL1(0x7ef8f6027720))(0x7ef8f60287f0)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60))(0x7ef8f6030710)],                         |
|        [T_HIDDEN_PK(0x7ef8f60283e0)]), filter(nil), rowset=16                                                                                                                                                              |
|   5 - output([T_HIDDEN_PK(0x7ef8f60283e0)], [ANONYMOUS_VIEW1.COL1(0x7ef8f6027720)], [ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60)]), filter(nil), rowset=16                                                                        |
|   6 - output([T_HIDDEN_PK(0x7ef8f60283e0)], [ANONYMOUS_VIEW1.COL1(0x7ef8f6027720)], [ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60)]), filter(nil), rowset=16                                                                        |
|       dop=16                                                                                                                                                                                                               |
|   7 - output([ANONYMOUS_VIEW1.COL1(0x7ef8f6027720)], [ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60)]), filter(nil), rowset=16                                                                                                       |
|       access([ANONYMOUS_VIEW1.COL1(0x7ef8f6027720)], [ANONYMOUS_VIEW1.COL3(0x7ef8f6027b60)])                                                                                                                               |
|   8 - output([T2.COL1(0x7ef8f60264c0)], [T2.COL3(0x7ef8f6026f90)]), filter(nil), rowset=16                                                                                                                                 |
|   9 - output([T2.COL1(0x7ef8f60264c0)], [T2.COL3(0x7ef8f6026f90)]), filter(nil), rowset=16                                                                                                                                 |
|       access([T2.COL1(0x7ef8f60264c0)], [T2.COL3(0x7ef8f6026f90)]), partitions(p0)                                                                                                                                         |
|       is_index_back=false, is_global_index=false,                                                                                                                                                                          |
|       range_key([T2.__pk_increment(0x7ef8f60584a0)]), range(MIN ; MAX)always true                                                                                                                                          |
| Used Hint:                                                                                                                                                                                                                 |
| -------------------------------------                                                                                                                                                                                      |
|   /*+                                                                                                                                                                                                                      |
|                                                                                                                                                                                                                            |
|       USE_PLAN_CACHE( NONE )                                                                                                                                                                                               |
|       PARALLEL(16)                                                                                                                                                                                                         |
|       ENABLE_PARALLEL_DML                                                                                                                                                                                                  |
|       DIRECT(TRUE, 0, 'FULL')                                                                                                                                                                                              |
|   */                                                                                                                                                                                                                       |
| Qb name trace:                                                                                                                                                                                                             |
| -------------------------------------                                                                                                                                                                                      |
|   stmt_id:0, stmt_type:T_EXPLAIN                                                                                                                                                                                           |
|   stmt_id:1, INS$1                                                                                                                                                                                                         |
|   stmt_id:2, SEL$1                                                                                                                                                                                                         |
| Outline Data:                                                                                                                                                                                                              |
| -------------------------------------                                                                                                                                                                                      |
|   /*+                                                                                                                                                                                                                      |
|       BEGIN_OUTLINE_DATA                                                                                                                                                                                                   |
|       PARALLEL(@"SEL$1" "T2"@"SEL$1" 16)                                                                                                                                                                                   |
|       FULL(@"SEL$1" "T2"@"SEL$1")                                                                                                                                                                                          |
|       USE_COLUMN_TABLE(@"SEL$1" "T2"@"SEL$1")                                                                                                                                                                              |
|       USE_PLAN_CACHE( NONE )                                                                                                                                                                                               |
|       PARALLEL(16)                                                                                                                                                                                                         |
|       ENABLE_PARALLEL_DML                                                                                                                                                                                                  |
|       OPTIMIZER_FEATURES_ENABLE('4.3.3.0')                                                                                                                                                                                 |
|       DIRECT(TRUE, 0, 'FULL')                                                                                                                                                                                              |
|       END_OUTLINE_DATA                                                                                                                                                                                                     |
|   */                                                                                                                                                                                                                       |
| Optimization Info:                                                                                                                                                                                                         |
| -------------------------------------                                                                                                                                                                                      |
|   T2:                                                                                                                                                                                                                      |
|       table_rows:3                                                                                                                                                                                                         |
|       physical_range_rows:3                                                                                                                                                                                                |
|       logical_range_rows:3                                                                                                                                                                                                 |
|       index_back_rows:0                                                                                                                                                                                                    |
|       output_rows:3                                                                                                                                                                                                        |
|       table_dop:16                                                                                                                                                                                                         |
|       dop_method:Global DOP                                                                                                                                                                                                |
|       avaiable_index_name:[TBL4]                                                                                                                                                                                           |
|       stats info:[version=0, is_locked=0, is_expired=0]                                                                                                                                                                    |
|       dynamic sampling level:0                                                                                                                                                                                             |
|       estimation method:[DEFAULT, STORAGE]                                                                                                                                                                                 |
|   Plan Type:                                                                                                                                                                                                               |
|       DISTRIBUTED                                                                                                                                                                                                          |
|   Note:                                                                                                                                                                                                                    |
|       Degree of Parallelism is 16 because of hint                                                                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
82 rows in set (0.006 sec)

示例 2：对于 Range-Hash 二级分区表，指定一级分区旁路导入数据。

目标表为二级分区，一级为 Range 分区，二级为 Hash 分区，指定一级分区全量旁路导入。

创建包含两个分区的表 tbl1，一级为 Range 分区，二级为 Hash 分区。

obclient [test]> CREATE TABLE tbl1_1 (
                     col1 INT,
                     col2 INT
                  )
                  PARTITION BY RANGE (col1)   
                  SUBPARTITION BY HASH (col2)
                  SUBPARTITION TEMPLATE (     
                     SUBPARTITION sp1,
                     SUBPARTITION sp2,
                     SUBPARTITION sp3
                  )
                  (
                     PARTITION p0 VALUES LESS THAN (10),
                     PARTITION p1 VALUES LESS THAN (20)  
                  );

使用旁路导入将表 tbl2 中的数据导入到表 tbl1 的 p0、p1 分区中。

obclient [test]> insert /*+ direct(true, 0, 'full') enable_parallel_dml parallel(3) append */ into tbl1 partition(p0,p1) select * from tbl2 where col1 <20;

使用 CREATE TABLE AS SELECT 语句旁路导入数据

CREATE TABLE AS SELECT 语句通过设置 DIRECT() Hint 来指定旁路导入的导数方式，在未指定 Hint 时能基于配置项 default_load_mode 确定导入数据的行为。

使用语法

CREATE /*+ [APPEND | DIRECT(need_sort,max_error,load_type)] parallel(N) */ TABLE table_name [AS] select_sentence

更多 CREATE TABLE 语法的信息，请参见 CREATE TABLE（MySQL 模式）和 CREATE TABLE（Oracle 模式）。

参数解释：

参数描述

APPEND | DIRECT()

使用 Hint 启用旁路导入功能。

APPEND Hint 默认等同于使用的 DIRECT(true, 0)，同时可以实现在线收集统计信息（GATHER_OPTIMIZER_STATISTICS Hint）的功能。
DIRECT() 参数解释如下：
- need_sort：表示写入的数据是否需要排序，值为 bool 类型：
  - true：表示需要排序。
  - false：表示不需要排序。
- max_error：表示最大容忍的错误行数。值为 INT 类型，超过这个数值导入任务执行会失败。
- full：表示全量旁路导入，可选项，取值须使用英文单引号包起来。

parallel(N) 加载数据的并行度，必填项，取值是大于 1 的整数。

使用示例

MySQL 模式

Oracle 模式

使用旁路导入将表 tbl1 中的数据导入到其他表中。

创建表 tbl1。

obclient [test]> CREATE TABLE tbl1(c1 int);

插入数据到表 tbl1。

obclient [test]> INSERT INTO tbl1 VALUES (1),(2),(3);

查询表 tbl1 中的数据。

obclient [test]> SELECT * FROM tbl1;

查询结果如下：

+------+
| c1   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.027 sec)

使用旁路导入将表 tbl1 中数据导入到表 tbl2。

指定 CREATE TABLE AS SELECT 语句的 Hint。

使用 APPEND Hint 旁路导入数据。

obclient [test]> CREATE /*+ append parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1;

使用 DIRECT Hint 旁路导入数据。

obclient [test]> CREATE /*+ direct(true, 0, 'full') parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1;

不指定 CREATE TABLE AS SELECT 语句的 Hint。

设置配置项 default_load_mode 的值为 FULL_DIRECT_WRITE。

obclient [test]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

obclient [test]> CREATE TABLE tbl2 AS SELECT * FROM tbl1;

验证表 tbl2 中是否已导入数据。

obclient [test]> SELECT * FROM tbl2;

查询结果如下：

+------+
| c1   |
+------+
|    1 |
|    3 |
|    2 |
+------+
3 rows in set (0.050 sec)

结果显示表 tbl2 中已导入数据。

使用旁路导入将表 tbl1 中的数据导入到其他表中。

创建表 tbl1。

obclient [SYS]> CREATE TABLE tbl1(c1 int);

插入数据到表 tbl1。

obclient [SYS]> INSERT INTO tbl1 VALUES (1),(2),(3);

查询表 tbl1 中的数据。

obclient [SYS]> SELECT * FROM tbl1;

查询结果如下：

+------+
| C1   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.045 sec)

使用旁路导入将表 tbl1 中数据导入到表 tbl2。

指定 CREATE TABLE AS SELECT 语句的 Hint。

使用 APPEND Hint 旁路导入数据。

obclient [SYS]> CREATE /*+ append parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1;

使用 DIRECT Hint 旁路导入数据。

obclient [SYS]> CREATE /*+ direct(true, 0, 'full') parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1;

不指定 CREATE TABLE AS SELECT 语句的 Hint。

设置 default_load_mode 的值为 FULL_DIRECT_WRITE。

obclient [SYS]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE';

obclient [SYS]> CREATE TABLE tbl2 AS SELECT * FROM tbl1;

验证表 tbl2 中是否已导入数据。

obclient [SYS]> SELECT * FROM tbl2;

查询结果如下：

+------+
| C1   |
+------+
|    3 |
|    2 |
|    1 |
+------+
3 rows in set (0.013 sec)

结果显示表 tbl2 中已导入数据。

注意事项

使用 LOAD DATA 语句旁路导入数据

注意

使用限制

使用语法

说明

说明

使用示例

说明

说明

注意

说明

注意

使用 INSERT INTO SELECT 语句旁路导入数据

使用限制

使用语法

说明

使用示例

使用 CREATE TABLE AS SELECT 语句旁路导入数据

使用语法

使用示例

相关文档