IBM Support

Performance Consideration When Using COALESCE Function in a SQL View Definition

Troubleshooting


Problem

The use of the COASLESCE function in a view definition has performance considerations if that view is used in an SQL JOIN.

Resolving The Problem

The use of the COASLESCE function in a view definition has performance considerations if that view is used in an SQL JOIN.

Consider the following simplistic example:

VIEW1 is a VIEW built over TABLE2 and TABLE3 with the following definition:

CREATE VIEW VIEW1 as
  SELECT COALESCE(A.FLD1, B.FLD1) as FLD1
  FROM TABLE2 A LEFT JOIN TABLE3 B
  ON A.FLD2 = B.FLD2

The view is used to join to TABLE1, for example:

SELECT FLD1
  FROM TABLE1 A join VIEW1 B
  ON A.FLD1 = B.FLD1

Because the join field from VIEW1 (B.FLD1) is the output from a COALESCE function, the join cannot easily be serviced by an index and the query optimizer may end up utilizing a less efficient data access method, such as a table scan.

This performance consideration should be considered when using the output of a COALESCE function in a view to join to a table.

Related Information

[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m3p0000006x0lAAA","label":"Performance-\u003ESolution Documents"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]

Historical Number

633568719

Document Information

More support for:
IBM i

Component:
Performance->Solution Documents

Software version:
All Versions

Operating system(s):
IBM i

Document number:
684469

Modified date:
29 October 2024

UID

nas8N1010987

Manage My Notification Subscriptions