ANALYZE DATABASE/SCHEMA/TABLES does a COMMIT after each table
Details
| Detail name | Value |
|---|---|
| Changelog Number | 5253 |
| Type | Improvement |
| Status | Resolved |
| Fix Versions | EXASOL 6.0.6 |
| Resolution Date | 2017-11-13 |
Background
The ANALYZE statement group does drop or refresh column statistics for a specified number of tables. A read lock is held on each of those tables for the duration of the complete statement.
The statement may take minutes to hours for many or very large tables.
Improvement
Holding many read locks for a longer time increases the probability of transaction conflicts with other sessions. Therefore a COMMIT has been added after each analyzed table to minimize read locking.
The single table variant ANALYZE TABLE does not COMMIT at all.
Benefit
Fewer transaction conflicts especially for ANALYZE DATABASE REFRESH STATISTICS.
Changed behavior
There is an implicit COMMIT now in ANALYZE multi table variants introduced in 6.0.4.
