hi,
For the following SQL and plan
=============== =============== =============== =============== ==
SELECT Q1.PLCY_KY, Q1.PLCY_TRM_EFC TV_DT, Q1.ACNTG_BGN_DT ,
CASE
WHEN ((Q1.PLCY_SRC_C D = '02') AND ((DAYS(Q1.PLCY_ TRM_EXPRTN_DT) -
DAYS(Q1.PLCY_TR M_EFCTV_DT)) <= 180))
THEN 'Y'
WHEN ((((((Q1.PLCY_S RC_CD = '01') AND
((DAYS(Q1.PLCY_ TRM_EXPRTN_DT) -
DAYS(Q1.PLCY_TR M_EFCTV_DT)) <= 360)) AND (Q1.CRPRTN_YR_C N
= 1))
AND (Q1.ANL_MNTNC_C D = '0')) AND ((Q1.ACTVTY_TRN CTN_CD =
'11')
OR (Q1.ACTVTY_TRNC TN_CD = '31'))) AND (((Q1.PRM_LOB_C D =
'001')
OR (Q1.PRM_LOB_CD = '003')) OR (Q1.PRM_LOB_CD = '004')))
THEN 'Y'
ELSE 'N' END , Q1.ISU_ACTVTY_T S
FROM EDW.PLCY_TRM AS Q1
WHERE (Q1.ACNTG_BGN_D T <= '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
SQL051203151901 8
=============== =============== =============== =============== =
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_D T(A)+Q1.$BLOCKI D$
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.IS U_ACTVTY_TS+Q1. PRM_LOB_CD
+Q1.ACTVTY_TRNC TN_CD+Q1.ANL_MN TNC_CD
+Q1.CRPRTN_YR_C N+Q1.PLCY_TRM_E XPRTN_DT
+Q1.PLCY_SRC_CD +Q1.PLCY_TRM_EF CTV_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.AC NTG_BGN_DT
+Q2.PLCY_TRM_EF CTV_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_D T <= '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.SQL05120 3151901860
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_D T(A)+Q1.$BLOCKI D$
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_D T(A)+Q1.$BLOCKI D$
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
4  2845 
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: 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.SQL05120 31519018 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: 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.SQL05120 31519018 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
but the block index has only one column in it
CREATE INDEX SYSIBM.SQL05120 3151901860
ON EDW.PLCY_TRM
(ACNTG_BGN_DT ASC
)
PCTFREE 10
ALLOW REVERSE SCANS;
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 function name:
void MyFunction(int x, int y);
Then when he instantiates a class he calls it:
MyClass myclass;
myclass (x, y);
|
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%, that is wildcard searches.
The strange thing is that if one of the parameters has the value %, and
the other one a%, the performance is very bad.
If i subsistute the variables with exactly the same values hardcoded in
|
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 be a bug in handling bytea columns with index and the
like-operator.
|
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
It took 14 seconds of CPU time to execute. After looking up the
documentation on the FETCH FIRST notation I find "Limiting the result
table to the first integer rows can improve performance. The database
|
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 like to read
more about this useful SQL operator. Suggestions?
SELECT CustID, OutType, EXISTS (SELECT CustID FROM tblCorrespondence
AS tblC
WHERE tblC.CustID = tblCorrespondence.CustID AND tblC.OutType = "01")
| | |
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 and _col which are
respectively the array to store the data in, the number of rows and the
number of columns. _m is of type T, and is size _row * _col * sizeof
(T).
I've managed to overload the subscript operator like this:
|
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
{
virtual Base& operator = (const Base &k) {}
};
|
by: subramanian100in |
last post by:
Consider the following program:
#include <iostream>
#include <cstdlib>
using namespace std;
class Test
{
public:
|
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 CPU. We are running x64 SQL 2005 SP2 on Windows 2003 SP2.
We thought it might be parameter sniffing that was causing the bad plan, so we re-wrote the query to disable parameter sniffing. Unfortunately, that made the problem worse as we always...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
