Optimize Temp Table
Enables the OPTIMIZE command on TEMP Tables to compact small files into larger ones, reducing read overhead and improving query performance.
When enabled, the generated Databricks load code adds OPTIMIZE after the load of intermediate temp tables in the source and Data Vault load steps. In these steps the statement is added only where the intermediate result is a table. An intermediate result held in a temporary view (see Use Temporary Views) gets no statement. When Analyze Temp Table is also enabled, the OPTIMIZE statement follows the ANALYZE TABLE statement.
Delete detection loads on Databricks apply the same setting to their landing, cache and end date temp objects and to the tables that receive the delete records.
When to Change This Setting
Enable it when loads leave many small files in the temp tables and later steps of the load read from them. Keep the default N when the extra step per temp table is not wanted.
Related Settings
| Setting | Interaction |
|---|---|
| Optimize Table | The same compaction step for the target tables of the load |
| Analyze Temp Table | The companion statistics step for the same temp tables |
| Use Temporary Views | Defaults to Y. In the source and Data Vault load steps, intermediate results held in temporary views are not optimized |
Notes
- This setting is part of the Databricks Options settings category.
- The default value for this setting is
N.