Access plan question

[P Adhia]

Hi,

When I explain the following SQL, the optimizer conjures up two columns
$C0 and $C1. It appears that they might refer to min and max values for
the range of values returned by the sub-select. Is that correct?

From the access plan graph it appears that, SIEBEL.S_EVT_ACT is the
first table to get accessed, before the sub-select is evaluated. If I
interpreted the graph correctly, I don't see how optimizer can determine
$C0 and $C1 before accessing the SIEBEL.EIM_ACTIVITY table which is in
the sub-select?

TIA

P Adhia
Original Statement:
------------------
SELECT 'touch'
FROM siebel.S_EVT_ACT
WHERE (ROW_ID IN
(SELECT T_ACT_EMP_ACTIVI
FROM siebel.EIM_ACTIVITY
WHERE (IF_ROW_BATCH_NUM = 501 AND T_ACT_EMP__EXS = 'N' AND
T_ACT_EMP__STA
= 0 AND T_ACT_EMP__UNQ = 'Y' AND IF_ROW_STAT_NUM = 0)))
FOR UPDATE
Optimized Statement:
-------------------
SELECT 'touch'
FROM SIEBEL.S_EVT_ACT AS Q3
WHERE (Q3.ROW_ID <= $C1) AND (Q3.ROW_ID >= $C0) AND Q3.ROW_ID = ANY
(SELECT DISTINCT Q1.T_ACT_EMP_ACTIVI, $C0, $C1
FROM SIEBEL.EIM_ACTIVITY AS Q1
WHERE (Q1.IF_ROW_STAT_NUM = +0000000000.) AND (Q1.T_ACT_EMP__UNQ = 'Y')
AND (Q1.T_ACT_EMP__STA = +0000000000.) AND (Q1.T_ACT_EMP__EXS =
'N') AND (Q1.IF_ROW_BATCH_NUM = +000000000000501.)
ORDER BY Q1.T_ACT_EMP_ACTIVI)

Access Plan:
-----------
Total Cost: 46898.9
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
2477.66
FETCH
( 2)
46898.9
3626.87
/---+---\
2477.66 8.62797e+007
TBSCAN TABLE: SIEBEL
( 3) S_EVT_ACT
14650.7
1118.51
|
2477.66
TEMP
( 4)
14648.1
1118.51
|
2477.66
FETCH
( 5)
14646.8
1118.51
+------------+------------+
4955.33 1 8.62797e+007
IXSCAN TBSCAN TABLE: SIEBEL
( 6) ( 7) S_EVT_ACT
77.8267 12.9039
5.75398 1
| |
8.62797e+007 1
INDEX: SIEBEL TEMP
S_EVT_ACT_P1 ( 8)
12.8886
1
|
1
UNIQUE
( 9)
12.883
1
|
1
IXSCAN
( 10)
12.8829
1
|
78750
INDEX: SIEBEL
EIM_ACTIVITY_PA7

[jiangqs]

Try this,

SELECT 'touch'
FROM siebel.S_EVT_ACT
WHERE ROW_ID =
(SELECT ROW_ID
FROM siebel.S_EVT_ACT
INTERSECT
SELECT T_ACT_EMP_ACTIVI
FROM FROM siebel.EIM_ACTIVITY
WHERE IF_ROW_BATCH_NUM = 501
AND T_ACT_EMP__EXS = 'N'
AND T_ACT_EMP__STA = 0
AND T_ACT_EMP__UNQ = 'Y'
AND IF_ROW_STAT_NUM = 0)
P Adhia wrote:

Hi,

When I explain the following SQL, the optimizer conjures up two columns
$C0 and $C1. It appears that they might refer to min and max values for
the range of values returned by the sub-select. Is that correct?

From the access plan graph it appears that, SIEBEL.S_EVT_ACT is the
first table to get accessed, before the sub-select is evaluated. If I
interpreted the graph correctly, I don't see how optimizer can determine
$C0 and $C1 before accessing the SIEBEL.EIM_ACTIVITY table which is in
the sub-select?

TIA

P Adhia
Original Statement:
------------------
SELECT 'touch'
FROM siebel.S_EVT_ACT
WHERE (ROW_ID IN
(SELECT T_ACT_EMP_ACTIVI
FROM siebel.EIM_ACTIVITY
WHERE (IF_ROW_BATCH_NUM = 501 AND T_ACT_EMP__EXS = 'N' AND
T_ACT_EMP__STA
= 0 AND T_ACT_EMP__UNQ = 'Y' AND IF_ROW_STAT_NUM = 0)))
FOR UPDATE
Optimized Statement:
-------------------
SELECT 'touch'
FROM SIEBEL.S_EVT_ACT AS Q3
WHERE (Q3.ROW_ID <= $C1) AND (Q3.ROW_ID >= $C0) AND Q3.ROW_ID = ANY
(SELECT DISTINCT Q1.T_ACT_EMP_ACTIVI, $C0, $C1
FROM SIEBEL.EIM_ACTIVITY AS Q1
WHERE (Q1.IF_ROW_STAT_NUM = +0000000000.) AND (Q1.T_ACT_EMP__UNQ = 'Y')
AND (Q1.T_ACT_EMP__STA = +0000000000.) AND (Q1.T_ACT_EMP__EXS =
'N') AND (Q1.IF_ROW_BATCH_NUM = +000000000000501.)
ORDER BY Q1.T_ACT_EMP_ACTIVI)

Access Plan:
-----------
Total Cost: 46898.9
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
2477.66
FETCH
( 2)
46898.9
3626.87
/---+---\
2477.66 8.62797e+007
TBSCAN TABLE: SIEBEL
( 3) S_EVT_ACT
14650.7
1118.51
|
2477.66
TEMP
( 4)
14648.1
1118.51
|
2477.66
FETCH
( 5)
14646.8
1118.51
+------------+------------+
4955.33 1 8.62797e+007
IXSCAN TBSCAN TABLE: SIEBEL
( 6) ( 7) S_EVT_ACT
77.8267 12.9039
5.75398 1
| |
8.62797e+007 1
INDEX: SIEBEL TEMP
S_EVT_ACT_P1 ( 8)
12.8886
1
|
1
UNIQUE
( 9)
12.883
1
|
1
IXSCAN
( 10)
12.8829
1
|
78750
INDEX: SIEBEL
EIM_ACTIVITY_PA7

[P. Adhia]

ji*****@gmail.com wrote:

Try this,

SELECT 'touch'
FROM siebel.S_EVT_ACT
WHERE ROW_ID =
(SELECT ROW_ID
FROM siebel.S_EVT_ACT
INTERSECT
SELECT T_ACT_EMP_ACTIVI
FROM FROM siebel.EIM_ACTIVITY
WHERE IF_ROW_BATCH_NUM = 501
AND T_ACT_EMP__EXS = 'N'
AND T_ACT_EMP__STA = 0
AND T_ACT_EMP__UNQ = 'Y'
AND IF_ROW_STAT_NUM = 0)

Thanks for the suggestion. I wasn't looking to get an alternative SQL.
I just wanted to understand how to interpret the access plan.

Reading from left to right and bottom to up, I couldn't figure out how
can stream which is on left of an operator (FETCH) can receive input
from and depend on a stream which is on the right. Also, I can't say I
have seen FETCH operator with 3 input streams before. I know only the
most common variant, that is, FETCH operator used for indexed access to
table.

P Adhia

PS Although, I can't change the SQL, but if I could, why do you think
your version of the SQL is better? Do you think DB2 optimizer will
select a better access path?