This query tries to initialize an unscheduled merge of data parts for tables. Note that we generally recommend against using OPTIMIZE TABLE ... FINAL (see these docs) as its use case is meant for administration, not for daily operations.
Note
OPTIMIZE can't fix the Too many parts error.
Syntax
The OPTIMIZE query is supported for MergeTree family (including materialized views) and the Buffer engines. Other table engines aren't supported.
When OPTIMIZE is used with the ReplicatedMergeTree family of table engines, ClickHouse creates a task for merging and waits for execution on all replicas (if the alter_sync setting is set to 2) or on current replica (if the alter_sync setting is set to 1).
OPTIMIZE does not perform a merge for any reason, it does not notify the client. To enable notifications, use the optimize_throw_if_noop setting.PARTITION, only the specified partition is optimized. How to set partition expression.FINAL or FORCE, optimization is performed even when all the data is already in one part. You can control this behaviour with optimize_skip_merged_partitions. Also, the merge is forced even if concurrent merges are performed.DEDUPLICATE, then completely identical rows (unless by-clause is specified) will be deduplicated (all columns are compared), it makes sense only for the MergeTree engine.You can specify how long (in seconds) to wait for inactive replicas to execute OPTIMIZE queries by the replication_wait_for_inactive_replica_timeout setting.
Note
If the alter_sync is set to 2 and some replicas are not active for more than the time, specified by the replication_wait_for_inactive_replica_timeout setting, then an exception UNFINISHED is thrown.
If you want to perform deduplication on custom set of columns rather than on all, you can specify list of columns explicitly or use any combination of *, COLUMNS or EXCEPT expressions. The explicitly written or implicitly expanded list of columns must include all columns specified in row ordering expression (both primary and sorting keys) and partitioning expression (partitioning key).
Note
Notice that * behaves just like in SELECT: MATERIALIZED and ALIAS columns are not used for expansion.
Also, it is an error to specify empty list of columns, or write an expression that results in an empty list of columns, or deduplicate by an ALIAS column.
Syntax
Examples
Consider the table:
Result:
All following examples are executed against this state with 5 rows.
DEDUPLICATE
When columns for deduplication are not specified, all of them are taken into account. The row is removed only if all values in all columns are equal to corresponding values in the previous row:
Result:
DEDUPLICATE BY *
When columns are specified implicitly, the table is deduplicated by all columns that are not ALIAS or MATERIALIZED. Considering the table above, these are primary_key, secondary_key, value, and partition_key columns:
Result:
DEDUPLICATE BY * EXCEPT
Deduplicate by all columns that are not ALIAS or MATERIALIZED and explicitly not value: primary_key, secondary_key, and partition_key columns.
Result:
DEDUPLICATE BY <list of columns>
Deduplicate explicitly by primary_key, secondary_key, and partition_key columns:
Result:
DEDUPLICATE BY COLUMNS(<regex>)
Deduplicate by all columns matching a regex: primary_key, secondary_key, and partition_key columns:
Result:
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4