Example usage of indexes with triggers or procedures

This example shows how indexes can be applied to triggers or procedures. The example is a correlation between two types of events, Type 14 and Type 15, such that if they both occur on the same host, they are cleared.

create procedure correlation
begin
	for each x in alerts.status where Type = 14
	begin
		for each y in alerts.status where y.Node = x.Node 
			and y.AlertGroup = x.AlertGroup and Type = 15
		begin
			update alerts.status set Severity = 0 
			where Identifier in (y.Identifier, x.Identifier)
		end
	end
end;

In this example, any event of Type 14 and 15 will be cleared if both exist for the same node and AlertGroup.

If there are 10,000 events of Type 14 and 10,000 events of Type 15, and there are, on average, 10 events per unique Node, the following results are possible:

Without indexing, the inner WHERE clause will scan over 10,000 * 20,000 rows; that is, 200 million rows. This will be slow, and is the reason why nested FOR EACH ROW statements are not advisable without good indexing.
With an index on the Type column, the inner WHERE clause will scan 10,000 * 10,000 rows; that is, 100 million rows. This will be slow, but is half the number of rows scanned when indexing is not used.
With an index on the Node column, the inner WHERE clause will scan 10,000 * 10 rows; that is, 100,000 rows. This will be performant.

Note: The scan performed by the UPDATE statement will always use the primary key.