revdpoel
New User
Joined: 01 Nov 2006
Posts: 56
|
|
|
|
Hello
This is a part of a query.
Using COALESCE, is that stage 1 or 2?
T400.rek-nr and t401a.bbo-key are part of an index
LEFT JOIN GWTB400 T400
ON T400.REK_NR = COALESCE(T812.REK_NR1,T812.REK_NR2)
AND T400.MSRT_KEY = :W-MSRT-KEY
AND T400.VRWYD_DAT IS NULL
INNER JOIN GWTB400 D400
ON D400.REK_NR = -1
LEFT JOIN GWTB401 T401A
ON T401A.BBO_KEY = COALESCE(T400.BBO_KEY,D400.BBO_KEY)
AND T401A.VA_DAT <= :W-T403-DAT
AND T401A.TM_DAT >= :W-T403-DAT
thnx |
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| Code: |
DB2 manuals provide a complete description of when a predicate can be stage 2 versus stage 1. Generally, stage 2 occurs after data accesses and performs such actions as sorting and evaluation of functions and expressions. Stage 2 predicates cannot take advantage of indexes to limit the data access and are more expensive than stage 1 predicates because they are evaluated later in the processing.
Stage 2 predicates generally contain column expressions, correlated subqueries, CASE expressions, and so on. A predicate can also appear to be stage 1, but it can be processed as stage 2. For example, any predicate that is processed after a join operation is stage 2. Although DB2 promotes mismatched data types to stage 1 through casting (as of version DB2 V8), some predicates with mismatched data types are stage 2. One example is a range predicate comparing a character column to a character value that exceeds the length of the column. The following are examples of stage 2 predicates (EMPNO is a character column of fixed length 6):
SELECT LASTNAME, FIRSTNME
FROM EMP
WHERE EMPNO > '00000010'
SELECT LASTNAME, FIRSTNME
FROM EMP
WHERE SUBSTR(LASTNAME,1,1) = 'B' |
Why do use COALESCE in joins? There are other much better ways to do it? |
|
