INSERT-SELECT 操作

• 1: 匹配排序顺序
• 2: 相同分段

INSERT /*+direct*/ INTO destination SELECT * FROM source;

1 - 匹配排序顺序

在执行 INSERT-SELECT 操作时，要避免 INSERT 的排序阶段，请确保 SELECT 查询的排序顺序与目标表的投影排序顺序相匹配。

例如，在单节点数据库中：

=> CREATE TABLE source (col1 INT, col2 INT, col3 INT);
=> CREATE PROJECTION source_p (col1, col2, col3)
     AS SELECT col1, col2, col3 FROM source
     ORDER BY col1, col2, col3
     SEGMENTED BY HASH(col3)
     ALL NODES;
=> CREATE TABLE destination (col1 INT, col2 INT, col3 INT);
=> CREATE PROJECTION destination_p (col1, col2, col3)
     AS SELECT col1, col2, col3 FROM destination
     ORDER BY col1, col2, col3
     SEGMENTED BY HASH(col3)
     ALL NODES;

以下 INSERT 不需要排序，因为查询结果具有投影的列顺序：

=> INSERT /*+direct*/ INTO destination SELECT * FROM source;

以下 INSERT 需要排序，因为 SELECT 语句中的列顺序与投影顺序不匹配：

=> INSERT /*+direct*/ INTO destination SELECT col1, col3, col2 FROM source;

以下 INSERT 不需要排序。列的顺序不匹配，但是显式 ORDER BY 可以使输出在 Vertica 中按 c1、c3、c2 排序：

=> INSERT /*+direct*/ INTO destination SELECT col1, col3, col2 FROM source
      GROUP BY col1, col3, col2
      ORDER BY col1, col2, col3 ;

2 - 相同分段

执行从分段的源表到分段的目标表的 INSERT-SELECT 操作时，请在相同列上对两个投影都进行分段以避免重新分段数据，如以下示例所示：

CREATE TABLE source (col1 INT, col2 INT, col3 INT);
CREATE PROJECTION source_p (col1, col2, col3) AS
   SELECT col1, col2, col3 FROM source
   SEGMENTED BY HASH(col3) ALL NODES;
CREATE TABLE destination (col1 INT, col2 INT, col3 INT);
CREATE PROJECTION destination_p (col1, col2, col3) AS
    SELECT col1, col2, col3 FROM destination
    SEGMENTED BY HASH(col3) ALL NODES;
INSERT /*+direct*/ INTO destination SELECT * FROM source;