Skip to main content
Version: current

OPTIMIZE TABLE

Rewrite data 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 DATA USING BIN_PACK
      [ ( { 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 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 USING BIN_PACK

Rewriting the data files in the specified table to Dremio's optimal supported file size
OPTIMIZE TABLE demo.example_table 
   REWRITE DATA USING BIN_PACK (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 USING BIN_PACK (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 USING BIN_PACK (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 USING BIN_PACK (MIN_FILE_SIZE_MB=100, MAX_FILE_SIZE_MB=1000, TARGET_FILE_SIZE_MB=512)