Reorganize statements reset the 35 days index expiration time

Detail name	Value
Changelog Number	9916
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, Exasol 6.1.0, Exasol 6.2.0
Fix Versions	Exasol 8.8.0, Exasol 7.1.16
Resolution Date	2022-11-24

Background

Index creation
In EXASOL, indices are created automatically when they are needed in join operations, and are automatically maintained upon DML operations. They are also automatically deleted after 35 days since their last use, meaning since they were last read (creation time, join operations or index scans).

Reorganize
For efficiency reasons, when data is to be deleted from a table, it is not actually deleted but instead marked as deleted. When too many rows are marked as deleted (default 25% of the number of rows), a reorganize operation is triggered where the deleted rows are deleted and space is reclaimed; also, all indices are dropped and newly created. Note that a reorganize operation may be triggered manually as well using the reorganize table statement

Bug description

Whenever an index is recreated via the reorganize statement in Exasol, the old index is first dropped and then a new one is being created (note that this does not apply to a data reorganization triggered via a delete statement or to index rebuilds via update statements). Since we're talking about a new index this means that it will have expiration time of 35 days. This may mean that old indices that are not needed are being kept alive due to frequent reorganize statements.

Workaround

In case one is sure an index is not needed anymore, one can drop it explicitly using the following syntax:

DROP [LOCAL|GLOBAL] INDEX ON <table_name>(<comma-separated-column-list);