[DB2-L] Is this access path correct?

Walter Janißen

[DB2-L] Is this access path correct?
Hi Harry

DB2 could do a multiple index access, but this access path uses list prefetch and AFAIK accesstype 'N', which means IN-predicate, is mutually exclusive with list prefetch. Because of the OR-predicate DB2 can not use the index an COLF.

But I think, the following select will work better:

SELECT DISTINCT COLA, COLB , COLC, COLD, COLE
FROM CM_BK
WHERE
COLF iN
('1111410007081000','1111410007081000')
UNION
SELECT DISTINCT COLA, COLB , COLC, COLD, COLE
FROM CM_BK
WHERE
COLG IN
('111410009H8','111410009H81',
'111410009H0','111410009H08','111410009H081',
'111410009H0810','111410009H08100','111410009H081000')

Bitte richten Sie, falls Sie es nicht schon tun, in Zukunft alle E-Mails nicht mehr an Einzelpersonen, sondern
immer an das Postfach "L-DBA-Gruppenpostfach".
Ansonsten kann sich die Bearbeitung Ihrer E-Mail bedingt durch Urlaub, Gleittag, Krankheit, Projektarbeit in Köln,
ZEITplus o.ä. verzögern.
Vielen Dank!

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40477 Düsseldorf
Tel.: +49 211 477-2928
Fax: +49 211 477-2615
mailto:[login to unmask email]

Vorsitzender des Aufsichtsrates: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



________________________________
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Harry Garagoski
Gesendet: Mittwoch, 2. Dezember 2009 14:10
An: [login to unmask email]
Betreff: [DB2-L] Is this access path correct?


We have a query like this on a 300.000 row table and there is a index on COLF (unique) and on COLG (duplicate (not much))

SELECT DISTINCT COLA, COLB , COLC, COLD, COLE
FROM CM_BK
WHERE
COLF iN
('1111410007081000','1111410007081000')
OR
COLG IN
('111410009H8','111410009H81',
'111410009H0','111410009H08','111410009H081',
'111410009H0810','111410009H08100','111410009H081000')



According to the documentation : If 2 indexable predicates are combined with an OR, the result is indexable.
All the statistics are run, but the query's acces type = R with sort (Distinct). So it takes to long to run.


QB_PN_O M_AT_MC_I TB_IX F_UJOG_UJOG TLM_P_QBLOCK BINDTIME
---------+---------+---------+---------+---------+---------+---------+---------
01 01 0 0 R 00 N CM_BK S NNNN NNNN IS SELECT 0912011622
01 02 0 3 00 N NNNN YNNN SELECT 0912011622

Has any one an idea?


Regards,

Harry Garagoski




------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.



________________________________

[ http://www.idug.org/images/M_images/idug%20na3.jpg ] < http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Roy Boxwell

Re: Is this access path correct?
(in response to Walter Janißen)
are colg or even the colf VARCHAR by any chance?? and remember STATS STATS
STATS and STATS

ps What version of DB2 are you running?



Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Siegfried Fürst, Gerhard Schubert




Harry Garagoski <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
02.12.2009 14:10
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
[DB2-L] Is this access path correct?







We have a query like this on a 300.000 row table and there is a index
on COLF (unique) and on COLG (duplicate (not much))

SELECT DISTINCT COLA, COLB , COLC, COLD, COLE
FROM CM_BK
WHERE
COLF iN
('1111410007081000','1111410007081000')
OR
COLG IN
('111410009H8','111410009H81',
'111410009H0','111410009H08','111410009H081',
'111410009H0810','111410009H08100','111410009H081000')



According to the documentation : If 2 indexable predicates are combined
with an OR, the result is indexable.
All the statistics are run, but the query's acces type = R with sort
(Distinct). So it takes to long to run.


QB_PN_O M_AT_MC_I TB_IX F_UJOG_UJOG TLM_P_QBLOCK
BINDTIME
---------+---------+---------+---------+---------+---------+---------+---------

01 01 0 0 R 00 N CM_BK S NNNN NNNN IS SELECT
0912011622
01 02 0 3 00 N NNNN YNNN SELECT
0912011622

Has any one an idea?


Regards,

Harry Garagoski




------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.





The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Scott Hodgin

Re: Is this access path correct?
(in response to Roy Boxwell)
Out of curiosity, what does the plan say when you do this?



SELECT DISTINCT COLA, COLB , COLC, COLD, COLE
FROM CM_BK
WHERE
COLF iN
(?,?)
OR
COLG IN
(?,?,?,?,?,?,?,?)



Sometimes if you have hardcodes values, you give DB2 _too much_
information




Scott Hodgin

Database Administrator, MCITP
South Carolina Farm Bureau Insurance Company

[login to unmask email]



________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Harry
Garagoski
Sent: Wednesday, December 02, 2009 8:10 AM
To: [login to unmask email]
Subject: [DB2-L] Is this access path correct?




We have a query like this on a 300.000 row table and there is a index
on COLF (unique) and on COLG (duplicate (not much))

SELECT DISTINCT COLA, COLB , COLC, COLD, COLE
FROM CM_BK
WHERE
COLF iN
('1111410007081000','1111410007081000')
OR
COLG IN
('111410009H8','111410009H81',
'111410009H0','111410009H08','111410009H081',
'111410009H0810','111410009H08100','111410009H081000')



According to the documentation : If 2 indexable predicates are
combined with an OR, the result is indexable.
All the statistics are run, but the query's acces type = R with sort
(Distinct). So it takes to long to run.


QB_PN_O M_AT_MC_I TB_IX F_UJOG_UJOG TLM_P_QBLOCK
BINDTIME
---------+---------+---------+---------+---------+---------+---------+--
-------
01 01 0 0 R 00 N CM_BK S NNNN NNNN IS SELECT
0912011622
01 02 0 3 00 N NNNN YNNN SELECT
0912011622

Has any one an idea?


Regards,

Harry Garagoski





------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.




________________________________

IDUG - The Worldwide DB2 User Community!
< http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Tony Andrews

Re: Is this access path correct?
(in response to Scott Hodgin)
Are you executing on V8 or V9? A couple of things to consider are:



1) If the optimizer chose tablespace scan, then the filter factors
on those predicates are probably high enough

for the optimizer to warrant it. But DB2 may be wrong in its filter
factors if it does not have enough statistical

information on columns F and G. What statistics does DB2 have on these
columns?



2) How much data exactly will be returned out of each predicate?
Do you know the values coming in?



3) Do you use visual explain? Here is where you can see the filter
factors that DB2 has guessed on each predicate.




------------------------------------------------------------------------
---------

Tony Andrews | Trainer | Application Tuning Consultant

123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 |
[login to unmask email] <mailto:[login to unmask email]>
For more information about Themis, visit www.themisinc.com






From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Hodgin, Scott
Sent: Wednesday, December 02, 2009 8:47 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Is this access path correct?



Out of curiosity, what does the plan say when you do this?



SELECT DISTINCT COLA, COLB , COLC, COLD, COLE
FROM CM_BK
WHERE
COLF iN
(?,?)
OR
COLG IN
(?,?,?,?,?,?,?,?)



Sometimes if you have hardcodes values, you give DB2 _too much_
information




Scott Hodgin

Database Administrator, MCITP
South Carolina Farm Bureau Insurance Company

[login to unmask email]



________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Harry
Garagoski
Sent: Wednesday, December 02, 2009 8:10 AM
To: [login to unmask email]
Subject: [DB2-L] Is this access path correct?




We have a query like this on a 300.000 row table and there is a index
on COLF (unique) and on COLG (duplicate (not much))

SELECT DISTINCT COLA, COLB , COLC, COLD, COLE
FROM CM_BK
WHERE
COLF iN
('1111410007081000','1111410007081000')
OR
COLG IN
('111410009H8','111410009H81',
'111410009H0','111410009H08','111410009H081',
'111410009H0810','111410009H08100','111410009H081000')



According to the documentation : If 2 indexable predicates are
combined with an OR, the result is indexable.
All the statistics are run, but the query's acces type = R with sort
(Distinct). So it takes to long to run.


QB_PN_O M_AT_MC_I TB_IX F_UJOG_UJOG TLM_P_QBLOCK
BINDTIME
---------+---------+---------+---------+---------+---------+---------+--
-------
01 01 0 0 R 00 N CM_BK S NNNN NNNN IS SELECT
0912011622
01 02 0 3 00 N NNNN YNNN SELECT
0912011622

Has any one an idea?


Regards,

Harry Garagoski



------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.




________________________________

IDUG - The Worldwide DB2 User Community!
< http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >



________________________________

IDUG - The Worldwide DB2 User Community!
< http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L