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.

{{< codeheader "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

{{< codeheader "Rewriting the data files in the specified table to Dremio's optimal supported file size" >}}

OPTIMIZE TABLE demo.example_table

{{< codeheader "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

{{< codeheader "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)

{{< codeheader "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)

{{< codeheader "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)

{{< codeheader "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)