ROWID prevents use of persistent join indices

Detail name	Value
Changelog Number	6127
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0
Fix Versions	Exasol 6.0.11
Resolution Date	2018-06-29

Description

When the pseudo-column ROWID is selected from a table a join with that table will neither use an existing index on that table nor will a persistent index be created. A join with that table will always create a temporary index that is gone after the query.

Besides queries with ROWID this also affects the statement UPDATE SET FROM WHERE, because internally this uses a join on ROWID.

For large tables this might incur a significant performance penalty.

Examples

Regular Query:

SELECT t1.ROWID, t2.ROWID FROM t1 JOIN t2 ON (t1.i=t2.i);

UPDATE SET FROM WHERE

UPDATE t1 target
SET target.i = ut.i
FROM t1 source JOIN ut ON source.x = ut.x;

Fix

With the

Workaround

An UPDATE statement like above can usually be rewritten as a MERGE statement, avoiding usage of the problematic ROWID pseudocolumn:

MERGE INTO t1 target
USING ut
ON target.x = ut.x
SET target.i = ut.i