473,321 Members | 1,669 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

FETCH 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

May 23 '06 #1
4 2818
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

May 23 '06 #2
Ian
db2admin wrote:
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
May 24 '06 #3
Ian wrote:
db2admin wrote:
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 :-)

Here is the give-away, search for: BLOCKID

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
May 24 '06 #4
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;

May 24 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: BCC | last post by:
In looking through some code I have inherited, I notice a lot of places where the programmer used operator() as a function call: void operator() (int x, int y); Rather than an explicit...
6
by: pragile | last post by:
I have a stored procedure that has some problems with slow performance. The procedure has 2 parameters @firstname varchar(100) @lastname varchar(100) These parameters can have values like a%,...
0
by: Alvar Freude | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, the following I posted already on pgsql-bugs -- perhaps someone has a good workaround or fix or can say me that I'm wrong? There seems to...
8
by: Evan Smith | last post by:
During a routine performance check using an event monitor, I discovered a class of query whose performance has me baffled. The monitor captured: SELECT * FROM EWM_CASE fetch first 1 rows only...
15
by: MLH | last post by:
Mr Leigh Purvis gave me a very clever piece of SQL to accomplish what is probably an uncommon objective. In it, he uses the EXISTS operator. I can find no documentation on it in A97 HELP. I would...
16
by: Joseph Paterson | last post by:
Hello, I've created a class to store 2 dimensional matrices, and I've been trying to overload the operator, so access to the elements of the matrix is easy. I have 3 private variables, _m, _row...
2
by: sven.bauer | last post by:
Hi, I have a question following up the following slightly older posting: http://groups.google.de/group/comp.lang.c++/browse_thread/thread/40e52371e89806ae/52a3a6551f84d38b class Base {...
1
by: subramanian100in | last post by:
Consider the following program: #include <iostream> #include <cstdlib> using namespace std; class Test { public:
2
by: apollock | last post by:
Our application is having an issue where a query in a stored procedure periodically gets a bad plan with a Lazy Spool Operator that changes the query execution time from 5ms to 900ms and kills our...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.