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
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.
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
Was this topic helpful?
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