OPTIMIZE

功能描述

对目标表的指定数据进行重写。

目前仅Iceberg表支持该语法。

注意事项

• 如果用户指定了排序键，数据重写后，文件内数据有序，而无法保证全局有序。
• 建议选择重写条件时，按照分区维度便捷重写条件。

语法格式

OPTIMIZE table_name REWRITE DATA 
    [ WITH OPTIONS (option_key = option_value [, ...]) ]
    [ WHERE condition ]
    [ ORDER BY target_list [ ASC | DESC ] ]

参数说明

• table_name：目标表的名字。取值范围：已存在的表名。

• condition：一个返回boolean值的表达式，用于判断哪些文件需要被重写。
• target_list：数据重写的排序键。
• option_key, option_value：数据重写的配置选项。

示例

CREATE TABLE reason_t2
(
    TABLE_SK          INTEGER               ,
    TABLE_ID          VARCHAR(20)           ,
    TABLE_NA          VARCHAR(20)
) PARTITION BY (TABLE_SC VARCHAR(20)) STORE AS iceberg;

INSERT INTO reason_t2 VALUES (1, 'S01', 'StudentA', 'Xiangbei'),(2, 'T01', 'TeacherA', 'Lingnan'),(3, 'T02', 'TeacherB', 'Hainan');
INSERT INTO reason_t2 VALUES (4, 'S02', 'StudentX', 'Xiangbei'),(5, 'T03', 'TeacherX', 'Lingnan'),(6, 'T04', 'TeacherY', 'Hainan');

OPTIMIZE reason_t2 REWRITE DATA WITH OPTIONS ('rewrite-all' = 'true') WHERE TABLE_SC = 'Hainan';

使用ORDER BY语句查看TABLE_SC ='Lingnan'分区数据重写计划：

EXPLAIN VERBOSE OPTIMIZE reason_t2 REWRITE DATA WITH OPTIONS ('rewrite-all' = 'true') WHERE TABLE_SC = 'Lingnan' ORDER BY 1;

                                                                         QUERY EXEC INFO                                                                         
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
  id |                                operation                                | E-rows | E-distinct | E-width | E-costs 
 ----+-------------------------------------------------------------------------+--------+------------+---------+---------
   1 | ->  Row Adapter                                                         |   1000 |            |     178 | 73.68   
   2 |    ->  Vector Insert on reason_t2                                       |   1000 |            |     178 | 73.68   
   3 |       ->  Vector Sort                                                   |   1000 |            |     178 | 53.68   
   4 |          ->  Partitioned Vector Foreign Scan on reason_t2               |   1000 |            |     178 | 1.35    
                                                          Predicate Information (identified by plan id)                                                         
 ---------------------------------------------------------------------------------------------------------------------------------------------------------------
   4 --Partitioned Vector Foreign Scan on reason_t2
         Server Type: lf
         Pruning results: (Manifests total: 3, Manifests left: 2, Files total: 4, Files dynamic prune 0, Files dynamic RF prune: 0, Files left after pruning: 2)
         DN read from: direct
         replication: 2 files 0.00 MB

设置min-file-size-bytes以重写全部目标文件：

EXPLAIN VERBOSE OPTIMIZE reason_t2 REWRITE DATA WITH OPTIONS ('min-file-size-bytes' = '0')  ORDER BY 1;