Use a composite index for alf_transaction id and commit_time_ms columns

Description

By default the indexes for the table alf_transaction are:

idx_alf_txn_ctms for commit_time_ms
fk_alf_txn_svr for server_id

and the id uses a primary key.

It is suggested by a customer with a large database (about 490M rows in alf_node) that the NodeServiceCleanup maintenance job that runs:

... may benefit from a composite index containing alf_transaction's id and commit_time_ms columns (where commit_time_ms is first in the order otherwise this will likely be ineffective).

Currently when the query is run, it can take an hour to two hours to run. A composite index may help cut down this time significantly.

Environment

None

Testcase ID

None

Assignee

Unassigned

Reporter

Harlin Seritt

Labels

None

Escalated By

None

Security Issue

None

ACT Numbers

00366828

Premier Customer

None

Code Branch

None

Build Location

None

Patch Attached

None

Dependent Version/s

None

Cloud or Enterprise

None

Prioritization Score

None

Bug Priority

Category 4

Delivery Team

None

Components

Affects versions