 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / May 2006FETCH operator in SQL plan
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 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 > hi, > 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 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 >> hi, > [quoted text clipped - 10 lines] > 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 :-) Here is the give-away, search for: BLOCKID  SignatureSerge 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; |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.