hi,
For the following SQL and plan
================================================== ============
SELECT Q1.PLCY_KY, Q1.PLCY_TRM_EFCTV_DT, Q1.ACNTG_BGN_DT,
CASE
WHEN ((Q1.PLCY_SRC_CD = '02') AND ((DAYS(Q1.PLCY_TRM_EXPRTN_DT) -
DAYS(Q1.PLCY_TRM_EFCTV_DT)) <= 180))
THEN 'Y'
WHEN ((((((Q1.PLCY_SRC_CD = '01') AND
((DAYS(Q1.PLCY_TRM_EXPRTN_DT) -
DAYS(Q1.PLCY_TRM_EFCTV_DT)) <= 360)) AND (Q1.CRPRTN_YR_CN
= 1))
AND (Q1.ANL_MNTNC_CD = '0')) AND ((Q1.ACTVTY_TRNCTN_CD =
'11')
OR (Q1.ACTVTY_TRNCTN_CD = '31'))) AND (((Q1.PRM_LOB_CD =
'001')
OR (Q1.PRM_LOB_CD = '003')) OR (Q1.PRM_LOB_CD = '004')))
THEN 'Y'
ELSE 'N' END , Q1.ISU_ACTVTY_TS
FROM EDW.PLCY_TRM AS Q1
WHERE (Q1.ACNTG_BGN_DT <= '04/07/2005') AND ('01/01/2004' <=
Q1.ACNTG_BGN_DT)
================================================== ============
4.68595e+06
FETCH
( 9)
534903
284968
/---+---\
8904.26 2.06425e+07
IXSCAN TABLE: EDW
( 10) PLCY_TRM
99.1518
7.71816
|
2.06425e+07
INDEX: SYSIBM
SQL0512031519018
================================================== ===========
9) FETCH : (Fetch)
Cumulative Total Cost: 534903
Cumulative CPU Cost: 1.14791e+10
Cumulative I/O Cost: 284968
Cumulative Re-Total Cost: 534903
Cumulative Re-CPU Cost: 1.14782e+10
Cumulative Re-I/O Cost: 284968
Cumulative First Row Cost: 34.9746
Cumulative Comm Cost: 0
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 284969
Arguments:
---------
BLKLOCK : (Block Lock intent)
NONE
MAXPAGES: (Maximum pages for prefetch)
1
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NONE
TABLOCK : (Table Lock intent)
INTENT NONE
TBISOLVL: (Table access Isolation Level)
UNCOMMITTED READ
Input Streams:
-------------
6) From Operator #10
Estimated number of rows: 8904.26
Partition Map ID: 4
Partitioning: (MULT )
Multiple Partitions
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.ACNTG_BGN_DT(A)+Q1.$BLOCKID$
Partition Column Names:
----------------------
+1: Q1.PLCY_KY
7) From Object EDW.PLCY_TRM
Estimated number of rows: 2.06425e+07
Partition Map ID: 4
Partitioning: (MULT )
Multiple Partitions
Number of columns: 10
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.$RID$+Q1.ISU_ACTVTY_TS+Q1.PRM_LOB_CD
+Q1.ACTVTY_TRNCTN_CD+Q1.ANL_MNTNC_CD
+Q1.CRPRTN_YR_CN+Q1.PLCY_TRM_EXPRTN_DT
+Q1.PLCY_SRC_CD+Q1.PLCY_TRM_EFCTV_DT+Q1.PLCY_KY
Partition Column Names:
----------------------
+1: Q1.PLCY_KY
Output Streams:
--------------
8) To Operator #8
Estimated number of rows: 4.68595e+06
Partition Map ID: 4
Partitioning: (MULT )
Multiple Partitions
Number of columns: 5
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.ISU_ACTVTY_TS+Q2.$C3+Q2.ACNTG_BGN_DT
+Q2.PLCY_TRM_EFCTV_DT+Q2.PLCY_KY
Partition Column Names:
----------------------
+1: Q2.PLCY_KY
10) IXSCAN: (Index Scan)
Cumulative Total Cost: 99.1518
Cumulative CPU Cost: 1.01432e+06
Cumulative I/O Cost: 7.71816
Cumulative Re-Total Cost: 0.0073248
Cumulative Re-CPU Cost: 20676.4
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 12.8102
Cumulative Comm Cost: 0
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 8.71816
Arguments:
---------
BLKLOCK : (Block Lock intent)
NONE
MAXPAGES: (Maximum pages for prefetch)
7
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NONE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT NONE
Predicates:
----------
11) Stop Key Predicate
Relational Operator: Less Than or Equal (<=)
Subquery Input Required: No
Filter Factor: 0.725565
Predicate Text:
--------------
(Q1.ACNTG_BGN_DT <= '04/07/2005')
12) Start Key Predicate
Relational Operator: Less Than or Equal (<=)
Subquery Input Required: No
Filter Factor: 0.50144
Predicate Text:
--------------
('01/01/2004' <= Q1.ACNTG_BGN_DT)
Input Streams:
-------------
5) From Object SYSIBM.SQL051203151901860
Estimated number of rows: 2.06425e+07
Partition Map ID: 4
Partitioning: (MULT )
Multiple Partitions
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.ACNTG_BGN_DT(A)+Q1.$BLOCKID$
Partition Column Names:
----------------------
+1: Q1.PLCY_KY
Output Streams:
--------------
6) To Operator #9
Estimated number of rows: 8904.26
Partition Map ID: 4
Partitioning: (MULT )
Multiple Partitions
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.ACNTG_BGN_DT(A)+Q1.$BLOCKID$
Partition Column Names:
----------------------
+1: Q1.PLCY_KY
================================================== =============
Now my question is
Why FETCH is returning 4.68595e+06 rows after it is receiving only
8904.26 rows from INDEX SCAN. selectivity was already decided by INDEX
There are no additional Table level predicates
regards,
jag