Guys,

DB2 LUW v9.5 fp5

Not sure if I am missing something obvious, but:

select MIN(COLLECT_TIME), MAX(COLLECT_TIME) from
RTM.TBL_COLLECT_IOSTAT_AIX_RTM where MACHINE_ID=?

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

Rows
RETURN
( 1)
Cost
I/O
NLJOIN
( 2)
38.4499
9.59622
/-----+------\
1 1
GRPBY GRPBY
( 3) ( 5)
19.2247 19.2247
4.79809 4.79813
IXSCAN IXSCAN
( 4) ( 6)
5428.06 5428
1354.74 1354.74
INDEX: RTM INDEX: RTM
IIOSTAT_AIX IIOSTAT_AIX
Q1 Q4

Index IIOSTAT_AIX description:

COLNAMES REVERSE_SCANS
-------------------------------------------------- -------------
+MACHINE_ID+COLLECT_TIME+DISK_NAME Y


Since the above query runs for quite some time, I tried db2advis out
of curiosity:

db2advis -d RTMDB -s "select MIN(COLLECT_TIME), MAX(COLLECT_TIME) from
RTM.TBL_COLLECT_IOSTAT_AIX_RTM where MACHINE_ID=?"

Suggested index:

CREATE INDEX "DB2INST1"."IDX101142031030000" ON "RTM
"."TBL_COLLECT_IOSTAT_AIX_RTM"
("MACHINE_ID" ASC, "COLLECT_TIME" DESC) ALLOW REVERSE
SCANS COLLECT SAMPLED DETAILED STATISTICS;


Since IIOSTAT_AIX already has REVERSE_SCANS enabled, I thought the
optimizer could use it for both MIN / MAX aggregations in the query.

Does REVERSE_SCAN apply to all columns in the index, or only to the
first one ?

Thanks,

-Michel

On 2011-01-14 21:38, Michel Esber wrote:
If I've got it right "allow reverse scans" means that you can scan leaf
pages in the index in both directions, i.e all columns. I doubt that the
proposed index will improve anything, what performance gain does
db2advis think it will give you?

What is the output of:

select FIRSTKEYCARD, FIRST2KEYCARD
from syscat.indexes
where indname = 'IIOSTAT_AIX'

Even though the index can be used to determine both min and max it might
not be beneficial to do so. If you have a large number of leaf pages and
a low FIRSTKEYCARD it will be expensive to scan the whole index, and
that is probably why the index is used twice, one for min and one for max.

Have you checked if there are lock waits involved, i.e. the table is
being updated? Other things that might be of interest is to look at
sysibmadm.snapdyn_sql for query in question.


Just some random thoughts

/Lennart

db2advis estimates 32% of performance improvement. I guess this is
fairly significant in this scenario.


Here:

FIRSTKEYCARD FIRST2KEYCARD
-------------------- --------------------
140 165893


Thanks again for your feedback,

-M

On Jan 14, 5:06*pm, Michel Esber <smes... (AT) gmail (DOT) com> wrote:
The recommended index will be much smaller than the existing index:
The existing index has 7.5M keys in it. The new index would have only
166K keys in it -- this will play a big role in making it faster. You
can see this by comparing NLEAF in syscat.indexes for the existing and
new indexes.

Regarding the individual column ordering:

Lennart was correct in saying that it just allows DB2 to scan the leaf
pages in reverse order. However, allowing reverse scans on an index
does not mean that you never need to have a index with a key in
descending order. It just means that in certain cases you can still
use an index without having to do a sort to put the index keys in
reverse order.


In this query, DB2 knows the value of MACHINE_ID at runtime, so it can
immediately go directly to the leaf page (or close to it) where that
value resides.

If DB2 was going to scan the index in reverse order, it would have to
start at the very end of the index, and scan every single leaf page
until it gets to one with the correct machine_id. Remember, DB2 does
*not* know what the next higher value of MACHINE_ID is.

So, eliminating reverse scans, then we can deduce:

If COLLECT_TIME is in descending order, then the very first key that
DB2 finds that has the correct MACHINE_ID will have the maximum
value.

If COLLECT_TIME is in ascending order, then DB2 will have to scan the
index – finding ALL keys with the correct MACHINE_ID –*and use the one
key with the highest COLLECT_TIME.


In a relatively small index, the difference between ASC and DESC may
be negligible.


Good luck,