The FLEX_DATA table is used to store generic primitive attribute values for which there are no separate table columns.

The current primary key is

BRANCH REV_MAX TYPE IDENTIFIER ATTR

At least some databases (including PostgreSQL) find this index unsuitable for answering the typical question about the attribute values of an object at a given point in time. If the database then decides to carry out a full-table scan instead, the performance for non-trivial data states drops to rock bottom.

A typical question against this table is

select * from FLEX_DATA where branch=? and type=? and identifier=? and rev_min <= [rev] and rev_max >= [rev]

where the value [rev], with which rev_min and rev_max are compared, is the same and is usually Long.MAX_VALUE for a query of the current status (but of course the optimizer of the DB knows nothing about this).

The idea of putting the rev_max column relatively far forward in the index stems from the consideration that you would like to store all current states (which you need frequently and which change) physically close together in the same blocks in order to have to load as few blocks as possible (the FLEX_DATA table is index-organized).

However, as the comparison value for rev_max in the queries is always given via a >= relation and never via an equality, this at least means that when searching for a value, a scan must be performed via the index starting from the specific value. De facto, this scan only affects exactly one value (Long.MAX_VALUE) for current queries, but some optimizers seem to be "so afraid" of this scan that they prefer to perform a full-table scan straight away, which leads to the fatal performance values described.

Experiments have shown that changing the index to the definition sequence of the columns

BRANCH TYPE IDENTIFIER REV_MAX ATTR

results in a dramatic performance gain, at least for PostgreSQL, as only then is the index used to answer the relevant questions.

The physical table layout changes for Oracle and MySQL in such a way that all data for one and the same object is stored close together, but the current states of all objects are scattered across the entire table. For changes to individual objects, this should ensure better performance, but for mass queries of the current statuses of several objects, more data must be loaded with the new layout.

Change

Update of the index to the (at least) more "attractive" order for PostgreSQL.

Data migration

For PostgreSQL (other DBs accordingly):

ALTER TABLE IF EXISTS "FLEX_DATA" DROP CONSTRAINT IF EXISTS "FLEX_DATA_pkey"; ALTER TABLE IF EXISTS "FLEX_DATA" ADD CONSTRAINT "FLEX_DATA_pkey" PRIMARY KEY ("BRANCH", "TYPE", "IDENTIFIER", "REV_MAX", "ATTR");

Migration on an existing system must be carried out manually by a database administrator. Newly set up systems are created directly in the new order.