473,890 Members | 1,704 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Question about MIN/MAX optimization

Hi all,

Db2 v8 FP15 LUW .

create table T (ID varchar (24), ABC timestamp)

There is an index for (ID, ABC), allowing reverse Scans.

My application needs to determine MIN and MAX(ABC) for a given ID. We
are currently using a simple statement:

select MIN(abc), MAX(abc) from T where ID = ? for read only

Table T has 100+ million rows, and several other applications are
reading/deleting data from it. The statement above runs with UR
isolation, however it takes a very long time to complete (5-10
minutes, or more).

I have studied the access plan, and it looks OK:

Access Plan:
-----------

Total Cost: 25.6855
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1
NLJOIN
( 2)
25.6855
3.99557
/-----+-----\
1 1
GRPBY GRPBY
( 3) ( 5)
12.8262 12.8587
1.99557 2
| |
46.1442 46.1442
IXSCAN IXSCAN
( 4) ( 6)
12.8544 12.8544
2 2
| |
973678 973678
INDEX: RTM INDEX: RTM
IPSSTAT_WIN IPSSTAT_WIN
I am looking for some magic SQL or hint that will allow me to improve
this rather 'simple' query.

PS: Yes, table and indexes do have updated statistics.

Thanks in advance,

-Michel
Jun 27 '08 #1
3 2966
Hi.
>
Try this. It will probably generate one indexs can instead of two.

with temp (abc) as
* * ( select abc from T were id *= ?)
select *max (abc), min(abc) from temp

/dg

DG, DB2 optimized the original statement into two index scans.

Original Statement:
------------------
with temp (COLLECT_TIME) as
(select COLLECT_TIME
from RTM.TBL_COLLECT _PSSTAT_WIN_RTM
where MACHINE_ID= ? and COLLECT_TIME ?)
select MIN(COLLECT_TIM E), MAX(COLLECT_TIM E)
from temp
Optimized Statement:
-------------------
SELECT Q6.$C0, Q3.$C0
FROM
(SELECT MAX(Q2.$C0)
FROM
(SELECT Q1.COLLECT_TIME
FROM RTM.TBL_COLLECT _PSSTAT_WIN_RTM AS Q1
WHERE (:? < Q1.COLLECT_TIME ) AND (Q1.MACHINE_ID = :?)) AS Q2) AS
Q3,
(SELECT MIN(Q5.$C0)
FROM
(SELECT Q4.COLLECT_TIME
FROM RTM.TBL_COLLECT _PSSTAT_WIN_RTM AS Q4
WHERE (:? < Q4.COLLECT_TIME ) AND (Q4.MACHINE_ID = :?)) AS Q5) AS
Q6

Access Plan:
-----------
Total Cost: 51.2774
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1
NLJOIN
( 2)
51.2774
7.9774
/-----+-----\
1 1
GRPBY GRPBY
( 3) ( 5)
25.6383 25.6383
3.98869 3.98871
| |
448.726 448.726
IXSCAN IXSCAN
( 4) ( 6)
87.5883 87.5879
13.6267 13.6267
| |
1.48278e+06 1.48278e+06
INDEX: RTM INDEX: RTM
IPSSTAT_WIN IPSSTAT_WIN

The overall cost is higher than the original statement :(

Thanks
Jun 27 '08 #2
This is the optimal plan we want. Can you reproduce it on DB2 V8 with
this DDL and DML?

Here is the plan with MIN/MAX:

Database Context:
----------------
Parallelism: None
CPU Speed: 3.778754e-07
Comm Speed: 0
Buffer Pool size: 165240
Sort Heap size: 1024
Database Heap size: 1024
Lock List size: 100
Maximum Lock List: 10
Average Applications: 1
Locks Available: 1020

Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability

---------------- STATEMENT 1 SECTION 203 ----------------
QUERYNO: 1
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1

Original Statement:
------------------
select MIN(ABC), MAX(ABC)
from T
where ID=?
Optimized Statement:
-------------------
SELECT Q6.$C0, Q3.$C0
FROM
(SELECT MAX(Q2.$C0)
FROM
(SELECT Q1.ABC
FROM DB2INST1.T AS Q1
WHERE (Q1.ID = :?)) AS Q2) AS Q3,
(SELECT MIN(Q5.$C0)
FROM
(SELECT Q4.ABC
FROM DB2INST1.T AS Q4
WHERE (Q4.ID = :?)) AS Q5) AS Q6

Access Plan:
-----------
Total Cost: 12.8453
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1
NLJOIN
( 2)
12.8453
2
/-----+-----\
1 1
GRPBY GRPBY
( 3) ( 5)
6.42335 6.42127
1 1
| |
2.92 2.92
IXSCAN IXSCAN
( 4) ( 6)
6.42298 6.42298
1 1
| |
73 73
INDEX: DB2INST1 INDEX: DB2INST1
I I


Extended Diagnostic Information:
--------------------------------

Diagnostic Identifier: 1
Diagnostic Details: EXP0022W Index has no statistics. The index
"DB2INST1". "I" has not had runstats run on it.
This
can lead to poor cardinality and predicate
filtering estimates.

Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 12.8453
Cumulative CPU Cost: 124849
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.0064907
Cumulative Re-CPU Cost: 17176.8
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 12.8441
Estimated Bufferpool Buffers: 3

Arguments:
---------
BLDLEVEL: (Build level)
DB2 v8.1.2.136 : special_19546
HEAPUSE : (Maximum Statement Heap Usage)
56 Pages
STMTHEAP: (Statement heap size)
4096

Input Streams:
-------------
7) From Operator #2

Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q7.$C1+Q7.$C0
2) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 12.8453
Cumulative CPU Cost: 124849
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.0064907
Cumulative Re-CPU Cost: 17176.8
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 12.8441
Estimated Bufferpool Buffers: 3

Arguments:
---------
EARLYOUT: (Early Out flag)
NONE
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE

Input Streams:
-------------
3) From Operator #3

Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q3.$C0

6) From Operator #5

Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q6.$C0
Output Streams:
--------------
7) To Operator #1

Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q7.$C1+Q7.$C0
3) GRPBY : (Group By)
Cumulative Total Cost: 6.42335
Cumulative CPU Cost: 61783.5
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.00503426
Cumulative Re-CPU Cost: 13322.5
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 6.4226
Estimated Bufferpool Buffers: 2

Arguments:
---------
AGGMODE : (Aggregration Mode)
COMPLETE
GROUPBYC: (Group By columns)
FALSE
GROUPBYN: (Number of Group By columns)
0
JN INPUT: (Join input leg)
OUTER
ONEFETCH: (One Fetch flag)
FALSE

Input Streams:
-------------
2) From Operator #4

Estimated number of rows: 2.92
Number of columns: 1
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q2.$C0
Output Streams:
--------------
3) To Operator #2

Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q3.$C0
4) IXSCAN: (Index Scan)
Cumulative Total Cost: 6.42298
Cumulative CPU Cost: 60803.5
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.00466394
Cumulative Re-CPU Cost: 12342.5
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 6.42108
Estimated Bufferpool Buffers: 2

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE

Predicates:
----------
2) Start Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04

Predicate Text:
--------------
(Q1.ID = :?)

2) Stop Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04

Predicate Text:
--------------
(Q1.ID = :?)
Input Streams:
-------------
1) From Object DB2INST1.I

Estimated number of rows: 73
Number of columns: 3
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q1.$RID$+Q1.ID +Q1.ABC

Output Streams:
--------------
2) To Operator #3

Estimated number of rows: 2.92
Number of columns: 1
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q2.$C0
5) GRPBY : (Group By)
Cumulative Total Cost: 6.42127
Cumulative CPU Cost: 61285.6
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.000783828
Cumulative Re-CPU Cost: 2074.3
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 6.42117
Estimated Bufferpool Buffers: 2

Arguments:
---------
AGGMODE : (Aggregration Mode)
COMPLETE
GROUPBYC: (Group By columns)
FALSE
GROUPBYN: (Number of Group By columns)
0
JN INPUT: (Join input leg)
INNER
ONEFETCH: (One Fetch flag)
TRUE

Input Streams:
-------------
5) From Operator #6

Estimated number of rows: 2.92
Number of columns: 1
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q5.$C0(A)
Output Streams:
--------------
6) To Operator #2

Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q6.$C0
6) IXSCAN: (Index Scan)
Cumulative Total Cost: 6.42298
Cumulative CPU Cost: 60803.5
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.00466394
Cumulative Re-CPU Cost: 12342.5
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 6.42108
Estimated Bufferpool Buffers: 2

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE

Predicates:
----------
3) Start Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04

Predicate Text:
--------------
(Q4.ID = :?)

3) Stop Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04

Predicate Text:
--------------
(Q4.ID = :?)
Input Streams:
-------------
4) From Object DB2INST1.I

Estimated number of rows: 73
Number of columns: 3
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q4.ABC(A)+Q4.$ RID$+Q4.ID
Output Streams:
--------------
5) To Operator #5

Estimated number of rows: 2.92
Number of columns: 1
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q5.$C0(A)
Objects Used in Access Plan:
---------------------------

Schema: DB2INST1
Name: T
Type: Table (reference only)

Schema: DB2INST1
Name: I
Type: Index
Time of creation:
2008-04-26-12.06.48.096459
Last statistics update:
Number of columns: 2
Number of rows: 73
Width of rows: -1
Number of buffer pool pages: 1
Distinct row values: No
Tablespace name:
IOSTATDATIDX
Tablespace overhead: 6.000000
Tablespace transfer rate: 0.400000
Source for statistics: Single Node
Prefetch page count: 128
Container extent page count: 32
Index clustering statistic: 80.000000
Index leaf pages: 2
Index tree levels: 2
Index full key cardinality: 25
Index first key cardinality: 25
Index first 2 keys cardinality: -1
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 2
Index page density: 100
Index avg sequential pages: -1
Index avg gap between sequences:-1
Index avg random pages: -1
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 0
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: DB2INST1
Base Table Name: T
Columns in index:
ID
ABC

Base Table For Index Not Already Shown:
---------------------------------------

Schema: DB2INST1
Name: T
Time of creation:
2008-04-26-12.06.12.993169
Last statistics update:
Number of columns: 2
Number of rows: -1
Number of pages: -1
Number of pages with rows: -1
Tablespace name: IOSTATDATIDX
Tablespace overhead: 6.000000
Tablespace transfer rate: 0.400000
Prefetch page count: 128
Container extent page count: 32
Table overflow record count: -1

Both plans are very similar. In terms of performance (CPU/Disk IO),
how do they compare ?

Thanks,
Jun 27 '08 #3
Michel Esber wrote:
>This is the optimal plan we want. Can you reproduce it on DB2 V8 with
this DDL and DML?

Here is a v8 plan using your solution. I will post another message
with the plan for MIN/MAX and group by.
Total Cost: 12.846
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1
NLJOIN
( 2)
12.846
2
/-----+-----\
1 1
IXSCAN IXSCAN
( 3) ( 4)
6.42298 6.42298
1 1
| |
73 73
INDEX: DB2INST1 INDEX: DB2INST1
I I
OK, so now the question is where is the difference between your original
scenario (not the best plan) and mine (best plan).
I recommend morphing it step by step and see where you loose your way.
Obviosuly the first step is to add data and statistics.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jun 27 '08 #4

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

Similar topics

10
4059
by: Will McGugan | last post by:
Hi, Are there any benefits in using a frozenset over a set, other than it being immutable? Will McGugan -- http://www.willmcgugan.com "".join({'*':'@','^':'.'}.get(c,0) or chr(97+(ord(c)-84)%26) for c in
18
2265
by: Exits Funnel | last post by:
Hello, I'm a little confused about where I should include header files and was wondering whether there was some convention. Imagine I've written a class foo and put the definition in foo.h and the implementation of its member functions in foo.cpp (which obviously #inludes foo.h) and further assume that it depends on a class bar which is defined in bar.h. It seems that there are the following two scenarios:
7
2346
by: Randell D. | last post by:
Folks, I have a Javascript performance question that I might have problems explaining... In PHP, better performance can be obtained dealing directly with a variable, as opposed to an element in an array... Thus, if I have a programming routine that utilises $a several times, it is better to write the value contained in $a to something else, for example, $vartmp, and have my routine instead use this for its work... I believe
83
3090
by: user | last post by:
Hello, Here is the program #include stdio int main(void) { const int num = 100; int *ip;
32
2228
by: Axel Bock | last post by:
Hi all, I am trying to get my head around what happens if I return a class object from a function. It seems C++ (MinGW) does not invoke the copy constructor if I do something like this: SomeObj foo() { SomeObj X;
45
4151
by: Robbie Hatley | last post by:
Hello, group. I've been doing too much C++ programming lately, and I'm starting to become rusty at some aspects of the C way of doing things, esp. efficient low-level data copies. Specificially, I just wrote the following, but I don't know if this is safe: void Func(char* left, char* right) { chat temp_left = {'\0'};
0
1314
by: Adam Sandler | last post by:
Hello, Using VWD 2005 here... I've noticed I've got .NET services on my system here: the .NET Runtime Optimization service and ASP.NET State service. I've noticed when the ASP.NET State service starts, it stops the .NET Runtime Optimization service. Does anyone know why this happens? Also, if the State service clobbers the Optimization service then why is the Optimization service there in the first place? Thanks!
40
1979
by: brad | last post by:
Will len(a_string) become a_string.len()? I was just reading http://docs.python.org/dev/3.0/whatsnew/3.0.html One of the criticisms of Python compared to other OO languages is that it isn't OO enough or as OO as others or that it is inconsistent. And little things such as this seem to support those arguments. Not that it matters really... just seems that classes with methods used in a more consistent manner would be more appropriate in...
30
3589
by: galiorenye | last post by:
Hi, Given this code: A** ppA = new A*; A *pA = NULL; for(int i = 0; i < 10; ++i) { pA = ppA; //do something with pA
4
1397
by: sagi | last post by:
Hello erveryone,I am a newcomer here and the word of c. Here I have a question confused me a lot that when I read codes I found some declaration like that: "int regcomp(regex_t *restrict comoiled, const char *restrict parttern, int cflags) " or something like that " int strnlen(const char FAR *s, int count)" what does the "restrict" or "FAR" means? --thanks advance
0
9815
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,...
0
10451
tracyyun
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...
0
9618
agi2029
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...
0
7160
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();...
0
5835
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...
0
6035
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4665
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4259
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3267
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.