1) filter is just an operation to apply predicate only:
Predicates:
----------
3) Residual Predicate
Comparison Operator:
Equal (=)
Subquery Input Required: No
Filter Factor:
6.62446e-07
Predicate Text:
--------------
(Q3.$C1 = Q1.ID)
Say from the TBSCAN(5) it came out 3 rows (1) (2) (3), and the outer leg gives (2), then filter operation will remove (1) (3) from the set and only send (2) into MSJN(2)
2)
From IXSCAN(7) we have:
Predicates:
----------
2) Start Key Predicate
Comparison Operator:
Equal (=)
Subquery Input Required: No
Filter Factor:
0.04
Predicate Text:
--------------
(Q1.RUNNB = 1)
2) Stop Key Predicate
Comparison Operator:
Equal (=)
Subquery Input Required: No
Filter Factor:
0.04
Predicate Text:
--------------
(Q1.RUNNB = 1)
This means the leading column is RUNNB.
From SORT(6) we have:
SORTKEY : (Sort Key column)
1: Q1.ID(A)
That means it need to be sorted based on Q1.ID, acs order...
3) For
29249
IXSCAN
( 7)
859.86
111.2
|
731225
INDEX: SYSTEM
IDX907020858520000
Q1
It doesn't do list prefetching because the output rows is 29249 and total number of rows in the PK is 731225, it will spend too much IO to do full list prefetch..
For IXSCAN(3), it can also not do prefetch because MSJOIN need a sorted result set, since it only need 3 rows, it will cost too much if doing full tablescan (then it need to read all rows from each record) and doing sort.
4) yes it is column 0 and column 1, from the input stream
SELECT Q4.$C0 AS "IDNB", Q4.$C1
FROM
(SELECT Q3.$C0, '1'
FROM HISDB.HDM_3_PRODRESULTS AS Q1 RIGHT OUTER JOIN
(SELECT Q2.IDNB, Q2.ID, Q2.FTPRATESAPPLICABLEFLAG
FROM DB2INST1.DM_PRODUCTS AS Q2
WHERE (Q2.FTPRATESAPPLICABLEFLAG = '1')) AS Q3 ON (Q1.RUNNB = 1) AND
(Q3.$C1 = Q1.ID)) AS Q4
5) round robin
|
