Skip to main content

OPTIMIZE TABLE

Rewrite data and manifest files to an optimal size, combining small files or splitting large files. For more information about optimizing tables, see Optimizing Tables.

Syntax
OPTIMIZE TABLE <table_name> 
   [ REWRITE MANIFESTS ]
   [ REWRITE DATA [ USING BIN_PACK ]
      [ FOR PARTITIONS <predicate> ]
      [ ( { TARGET_FILE_SIZE_MB | MIN_FILE_SIZE_MB | MAX_FILE_SIZE_MB | MIN_INPUT_FILES } = <value> [, ... ] ) ]
   ]

Parameters​

{{< sql-section file="data/sql/optimize.json" data="optimizingATable" >}}

Examples​

Rewriting the data files then the manifest files for in the specified table to Dremio's optimal supported file size
OPTIMIZE TABLE demo.example_table
Rewriting the data files in the specified table using the default bin-packing rewrite algorithm to Dremio's optimal supported file size
OPTIMIZE TABLE demo.example_table 
   REWRITE DATA
Rewriting the data files in the specified table to Dremio's optimal supported file size
OPTIMIZE TABLE demo.example_table 
   REWRITE DATA (TARGET_FILE_SIZE_MB=256)
Rewriting the data files in the specified table using the specified minimum and maximum file sizes to Dremio's optimal supported file size
OPTIMIZE TABLE demo.example_table 
   REWRITE DATA (MIN_FILE_SIZE_MB=100, MAX_FILE_SIZE_MB=1000)
Rewriting the data files in the specified table for the minimum number of specified files to Dremio's optimal supported file size
OPTIMIZE TABLE demo.example_table 
   REWRITE DATA (MIN_INPUT_FILES=10)
Rewriting the data files in the specified table using the specified minimum and maximum file sizes to the specified target file size
OPTIMIZE TABLE demo.example_table 
   REWRITE DATA (MIN_FILE_SIZE_MB=100, MAX_FILE_SIZE_MB=1000, TARGET_FILE_SIZE_MB=512)
Rewriting the specified partition where column 'sales_year' contains '2023'
OPTIMIZE TABLE demo.example_table
   FOR PARTITIONS sales_year=2023
Optimizing only Q4 data for the last 2 years
OPTIMIZE TABLE demo.example_table
   FOR PARTITIONS sales_year IN (2021, 2022) AND sales_month IN ('OCT', 'NOV', 'DEC')
Rewriting manifest files only
OPTIMIZE TABLE demo.example_table 
   REWRITE MANIFESTS