[DERBY-6522] Make LOB handling by MERGE statement more efficient.

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.11.1.1
• Component/s: SQL
• Urgency: Normal
• Bug behavior facts: Performance
• Assignee: Unassigned
• Reporter: Richard N. Hillegas
• Created: 20/Mar/14 14:47
• Updated: 20/Mar/14 14:48

Description

As a result of patch derby-3155-44-aa-lobsInTriggers.diff, LOBs may be materialized before being buffered up for use by the WHEN [ NOT ] MATCHED clauses of MERGE statements. This can happen, for instance, when a LOB is not referenced by any clause in the MERGE statement but the LOB is still needed by a trigger fired by one of the statement's INSERT, UPDATE, or DELETE actions.

For some applications, this will be inefficient. It may be possible to improve efficiency by leaving the driving left join open until the WHEN [ NOT ] MATCHED clauses have been processed and then just buffer up LOB references rather than materialized LOBs. However, that may cause other problems. In particular, we would need to make sure that LOBs which are referenced multiple times (say by more than one DELETE trigger) still work; we have had problems with LOBs which end up being read more than once by a statement.

Issue Links

• relates to: DERBY-3155 Support for SQL:2003 MERGE statement