Betr: [DB2-L] AW: [DB2-L] Is this access path correct?

Harry Garagoski

Betr: [DB2-L] AW: [DB2-L] Is this access path correct?
Hello Walter,

The UNION is better , but we are not able to change the SQL.
It is part of a external software. We are not happy with it.


Regard,

Harry Garagoski






Van:
Walter Janißen <[login to unmask email]>
Aan:
[login to unmask email]
Datum:
02-12-2009 14:39
Onderwerp:
[DB2-L] AW: [DB2-L] Is this access path correct?
Verzonden door:
IDUG DB2-L <[login to unmask email]>



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.





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



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



------------------------------------------------------------------------
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 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

Balachandran Chandrasekaran1

Re: Is this access path correct?
(in response to Harry Garagoski)
I just noticed this.

COLF iN
('1111410007081000','1111410007081000')

Any reason why the same value is mentioned twice ?





Harry Garagoski <[login to unmask email]>
Sent by: IDUG DB2-L <[login to unmask email]>
02/12/2009 18:40
Please respond to
IDUG DB2-L <[login to unmask email]>


To
[login to unmask email]
cc

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.





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: Betr: [DB2-L] AW: [DB2-L] Is this access path correct?
(in response to Balachandran Chandrasekaran1)
What happens if you add COLA, COLB, COLC, COLD, COLE to one or both indexes (or create new indexes with these additional columns) so no data pages have to be read.




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 9:17 AM
To: [login to unmask email]
Subject: [DB2-L] Betr: [DB2-L] AW: [DB2-L] Is this access path correct?




Hello Walter,

The UNION is better , but we are not able to change the SQL.
It is part of a external software. We are not happy with it.


Regard,

Harry Garagoski






Van:

Walter Janißen <[login to unmask email]>

Aan:

[login to unmask email]

Datum:

02-12-2009 14:39

Onderwerp:

[DB2-L] AW: [DB2-L] Is this access path correct?

Verzonden door:

IDUG DB2-L <[login to unmask email]>



________________________________




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] <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] <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.




________________________________

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 >



------------------------------------------------------------------------
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

Terry Purcell

Re: Betr: [DB2-L] AW: [DB2-L] Is this access path correct?
(in response to Scott Hodgin)
Harry,

I skimmed through the responses, and only Walter's suggestion will allow you
to have matching index access given these 2 IN lists. Walter is correct that IN
list access and multi-index access are mutually exclusive.

Regards
Terry Purcell

On Wed, 2 Dec 2009 15:16:44 +0100, Harry Garagoski
<[login to unmask email]> wrote:

>Hello Walter,
>
> The UNION is better , but we are not able to change the SQL.
> It is part of a external software. We are not happy with it.
>
>
>Regard,
>
>Harry Garagoski
>
>Van:
>Walter Janiߥn <[login to unmask email]>
>Aan:
>[login to unmask email]
>Datum:
>02-12-2009 14:39
>Onderwerp:
>[DB2-L] AW: [DB2-L] Is this access path correct?
>Verzonden door:
>IDUG DB2-L <[login to unmask email]>
>
>
>
>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?
>ZEITplus o.䮠verz?n.
>Vielen Dank!
>Mit freundlichen Gr?Walter Janiߥn
>
>ITERGO Informationstechnologie GmbH
>Anwendungsentwicklung
>Laufzeitarchitektur
>Victoriaplatz 2
>40477 D?orf
>Tel.: +49 211 477-2928
>Fax: +49 211 477-2615
>mailto:[login to unmask email]
>
>Vorsitzender des Aufsichtsrates: J?Vetter
>Gesch䦴sf?: Dr. Bettina Anders (Vorsitzende),
>Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Sch?
>
>Sitz: D?orf, Handelsregister: Amtsgericht D?orf, 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
>

_____________________________________________________________________

* 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