REORGANIZE may crash if duplicating indices do exist on a table
Details
| Detail name | Value |
|---|---|
| Changelog Number | 7662 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | EXASOL 6.0.0, Exasol 6.1.0 |
| Fix Versions | Exasol 6.0.15, Exasol 6.1.3 |
| Resolution Date | 2019-03-19 |
Background
Exasol creates indices on demand when query execution requires them. To reduce the number of indices the system reorders index columns to match already existing indices which are reused. So, for a certain set of index columns there are only two indices allowed: one local and one global index.
Parallel queries can create identical indices in their transactions. On commit, the index similarity is recognized and resolved to keep only one index.
Problem
Under rare circumstances the recognition of similar indices may fail leaving duplicating indices on the table. They will show up in EXA_DBA_INDICES having the same index columns but a differing index column order (REMARKS column).
Those indices cannot be dropped using DROP INDEX statement. REORGANIZE on the table will result in internal server error.
Fixes
The recognition of duplicating indices in commit will be fixed to avoid the situation.
REORGANIZE will be fixed to remove all but one of the duplicating indices.
DROP INDEX will be improved to drop all indices matching a given set of index columns.
Workaround
To postpone the delete reorganization of affected tables, the delete reorganization margin can be increased from 25% to e.g. 50% by setting the following database parameter
-deletedMarkedMargin=50
This buys time until the fix is available.
There is also the workaround to copy the table into a new one, re-grant any privileges, eventually enforce indices and drop the original table.
