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
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

Looks strange. Did you play with runstats on different partitions?

For partitioned database runstats is determined for one partition, and
then extrapolated to determine a suitable estimate for all partitions.

-- Artur Wronski

db2admin wrote:

The IXSCAN operator returns the number of matching keys. The fact that
the FETCH is returning more rows is an indication of the fact that the
index is not a unique index -- i.e. there are multiple RIDs per key.

In your case, the index SYSIBM.SQL0512031519018 is a block index (i.e.
your table uses MDC), in which case you will certainly get more than 1
row per index key. (If you don't, you have big problems :-)



Ian

Ian wrote:

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

but the block index has only one column in it

CREATE INDEX SYSIBM.SQL051203151901860
ON EDW.PLCY_TRM
(ACNTG_BGN_DT ASC
)
PCTFREE 10
ALLOW REVERSE SCANS;