Antwort: [DB2-L] Undertanding Access Paths

Roy Boxwell

Antwort: [DB2-L] Undertanding Access Paths
my gut reaction would be "size" - IO is also used in the access path and
so accessing a "small" index and then getting data (which must also be
done using the first index)
is probably quicker (even if you add in a small in memory sort as well)

FEC_SALIDA has 35 distinct values and so with 123981 rows thats a lot of
duplicates and possible a very small index io...

You could always try selectiong against SYSCOLDIST and see what is there
and of course doing KEYCARD. But use of host variables tends to lean
towards bad access paths some times...
just my friday thoughs before I pop off home for a beer!




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





Jair Montealegre <[login to unmask email]>
Gesendet von: DB2 Data Base Discussion List <[login to unmask email]>
12.12.2008 13:24
Bitte antworten an DB2 Database Discussion list at IDUG


An: [login to unmask email]
Kopie:
Thema: [DB2-L] Undertanding Access Paths


Hi guys, I am in an optimization group in my company and I have the
following situation: There's a table that has 123,981 rows with the
following indexes defined on it. P324.IWFMGPE1 COD_ESTADO 1 A FEC_SALIDA 2
A COD_MSWEMIB 3 A COD_VIAPAGO 4 A COD_DIISOALF 5 A IMP_VALOR 6 D
P324.IWFMGPE2 COD_NUMBERRE 1 A COD_ESTADO 2 A P324.IWFMGPE3 FEC_SALIDA 1 A
COD_ESTADO 2 A P324.IWFMGPEM (CLUSTER, UNIQUE) 100% cluster ratio
COD_NUMBER 1 A Columns listed in indexes above have the following
cardinality: COD_NUMBER 123891 COD_ESTADO 10 FEC_SALIDA 35 COD_MSWEMIB 3
COD_VIAPAGO 11 COD_DIISOALF 10 IMP_VALOR 67427 COD_NUMBERRE 123518 Now,
having this cursor: DECLARE CURSOR1 CURSOR FOR SELECT COD_ESTADO ,
FEC_SALIDA , COD_MSWEMIB , COD_VIAPAGO , COD_DIISOALF , IMP_VALOR ,
COD_NUMBER , COD_MSWEMIBR FROM TWFMGPEM WHERE COD_ESTADO
IN(40,42,45,20,21) AND FEC_SALIDA = :DCLTWFMGPEM.PEM-FEC-SALIDA ORDER BY
COD_ESTADO , FEC_SALIDA , COD_MSWEMIB , COD_VIAPAGO , COD_DIISOALF The
explain data says that the access that cursor performs will be thru index
IWFMGPE3 using 1 column (I1), List Prefetch and then performs SORT because
of the use of clause ORDER BY. Why does optimizer choose that access
path?. Isn't it better access path to use index IWFMGPE1, avoid performing
SORT and make an N2 access? Thanks in advance for your help. PS: Our
system is z/OS and DB2 V8 for z/OS Jair Montealegre Zorro Madrid -Spain
[login to unmask email]

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA
The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at IDUG.ORG under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Robert Catterall

Re: Antwort: [DB2-L] Undertanding Access Paths
(in response to Leslie)
I think that Roy is right on. Just because it seems to us that one access
path would be "better" than another, more often than not (given rich and
accurate catalog statistics) the path chosen by the DB2 optimizer is in fact
the right one.

How many rows are in the result set? If it's not too many (e.g., a few
hundred), the ORDER BY sort operation will complete very quickly and is no
big deal.

You could try forcing the use of the IWFMGPE1 index by adding the OPTIMIZE
FOR 1 ROW clause to the query (in that case DB2 will avoid the ORDER BY sort
if an appropriate index on the ORDER BY columns exists, and that's your
situation). You might find that without the sort the response time is no
better than what you see with list prefetch and an ORDER BY sort. If the
performance is significantly better without the sort, it may be that the DB2
optimizer is lacking some important catalog information pertaining to the
query. As Roy suggested, column value distribution statistics can be very
important to good access path selection.

Robert (still several hours away from an end-of-the-work-week adult
libation).


On Fri, Dec 12, 2008 at 9:18 AM, Roy Boxwell <[login to unmask email]> wrote:

>
> my gut reaction would be "size" - IO is also used in the access path and so
> accessing a "small" index and then getting data (which must also be done
> using the first index)
> is probably quicker (even if you add in a small in memory sort as well)
>
> FEC_SALIDA has 35 distinct values and so with 123981 rows thats a lot of
> duplicates and possible a very small index io...
>
> You could always try selectiong against SYSCOLDIST and see what is there
> and of course doing KEYCARD. But use of host variables tends to lean towards
> bad access paths some times...
> just my friday thoughs before I pop off home for a beer!
>
>
>
> *
> 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
> *
>
>
> *Jair Montealegre <[login to unmask email]>*
> Gesendet von: DB2 Data Base Discussion List <[login to unmask email]>
>
> 12.12.2008 13:24
> Bitte antworten an DB2 Database Discussion list at IDUG
>
> An: [login to unmask email]
> Kopie:
> Thema: [DB2-L] Undertanding Access Paths
>
>
>
> Hi guys, I am in an optimization group in my company and I have the
> following situation: There's a table that has 123,981 rows with the
> following indexes defined on it. P324.IWFMGPE1 COD_ESTADO 1 A FEC_SALIDA 2 A
> COD_MSWEMIB 3 A COD_VIAPAGO 4 A COD_DIISOALF 5 A IMP_VALOR 6 D P324.IWFMGPE2
> COD_NUMBERRE 1 A COD_ESTADO 2 A P324.IWFMGPE3 FEC_SALIDA 1 A COD_ESTADO 2 A
> P324.IWFMGPEM (CLUSTER, UNIQUE) 100% cluster ratio COD_NUMBER 1 A Columns
> listed in indexes above have the following cardinality: COD_NUMBER 123891
> COD_ESTADO 10 FEC_SALIDA 35 COD_MSWEMIB 3 COD_VIAPAGO 11 COD_DIISOALF 10
> IMP_VALOR 67427 COD_NUMBERRE 123518 Now, having this cursor: DECLARE CURSOR1
> CURSOR FOR SELECT COD_ESTADO , FEC_SALIDA , COD_MSWEMIB , COD_VIAPAGO ,
> COD_DIISOALF , IMP_VALOR , COD_NUMBER , COD_MSWEMIBR FROM TWFMGPEM WHERE
> COD_ESTADO IN(40,42,45,20,21) AND FEC_SALIDA = :DCLTWFMGPEM.PEM-FEC-SALIDA
> ORDER BY COD_ESTADO , FEC_SALIDA , COD_MSWEMIB , COD_VIAPAGO , COD_DIISOALF
> The explain data says that the access that cursor performs will be thru
> index IWFMGPE3 using 1 column (I1), List Prefetch and then performs SORT
> because of the use of clause ORDER BY. Why does optimizer choose that access
> path?. Isn't it better access path to use index IWFMGPE1, avoid performing
> SORT and make an N2 access? Thanks in advance for your help. PS: Our system
> is z/OS and DB2 V8 for z/OS Jair Montealegre Zorro Madrid -Spain
> [login to unmask email]
> ------------------------------
>
> *IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA * < http://idug.org/lsNA >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
> list archives, FAQ, and delivery preferences are at *IDUG.ORG* < http://www.idug.org/lsidug > under the Listserv tab. While at the site, you can also access the IDUG
> Online Learning Center, Tech Library and Code Place, see the latest IDUG *conference
> information* < http://www.idug.org/lsconf > , and much more. *If you have not
> yet signed up for Basic Membership in IDUG, available at no cost, click on
> **Member Services* < http://www.idug.org/lsms >
>
>
> ------------------------------
>
> *IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA * < http://idug.org/lsNA >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
> list archives, FAQ, and delivery preferences are at *IDUG.ORG < http://www.idug.org/lsidug >
> * under the Listserv tab. While at the site, you can also access the IDUG
> Online Learning Center, Tech Library and Code Place, see the latest IDUG *conference
> information < http://www.idug.org/lsconf > *, and much more. * If you have
> not yet signed up for Basic Membership in IDUG, available at no cost, click
> on Member Services < http://www.idug.org/lsms > *
>



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Leslie

Re: Undertanding Access Paths
(in response to Roy Boxwell)
Hi

You might wish to restructure that email . see below.

Leslie



_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Jair Montealegre
Sent: 12 December 2008 12:25
To: [login to unmask email]
Subject: [DB2-L] Undertanding Access Paths



Hi guys, I am in an optimization group in my company and I have the
following situation: There's a table that has 123,981 rows with the
following indexes defined on it. P324.IWFMGPE1 COD_ESTADO 1 A FEC_SALIDA 2 A
COD_MSWEMIB 3 A COD_VIAPAGO 4 A COD_DIISOALF 5 A IMP_VALOR 6 D P324.IWFMGPE2
COD_NUMBERRE 1 A COD_ESTADO 2 A P324.IWFMGPE3 FEC_SALIDA 1 A COD_ESTADO 2 A
P324.IWFMGPEM (CLUSTER, UNIQUE) 100% cluster ratio COD_NUMBER 1 A Columns
listed in indexes above have the following cardinality: COD_NUMBER 123891
COD_ESTADO 10 FEC_SALIDA 35 COD_MSWEMIB 3 COD_VIAPAGO 11 COD_DIISOALF 10
IMP_VALOR 67427 COD_NUMBERRE 123518 Now, having this cursor: DECLARE CURSOR1
CURSOR FOR SELECT COD_ESTADO , FEC_SALIDA , COD_MSWEMIB , COD_VIAPAGO ,
COD_DIISOALF , IMP_VALOR , COD_NUMBER , COD_MSWEMIBR FROM TWFMGPEM WHERE
COD_ESTADO IN(40,42,45,20,21) AND FEC_SALIDA = :DCLTWFMGPEM.PEM-FEC-SALIDA
ORDER BY COD_ESTADO , FEC_SALIDA , COD_MSWEMIB , COD_VIAPAGO , COD_DIISOALF
The explain data says that the access that cursor performs will be thru
index IWFMGPE3 using 1 column (I1), List Prefetch and then performs SORT
because of the use of clause ORDER BY. Why does optimizer choose that access
path?. Isn't it better access path to use index IWFMGPE1, avoid performing
SORT and make an N2 access? Thanks in advance for your help. PS: Our system
is z/OS and DB2 V8 for z/OS Jair Montealegre Zorro Madrid -Spain
[login to unmask email]

_____

< http://idug.org/lsNA > IDUG 2009 - North America * May 11-15, 2009 *
Denver, CO, USA

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at IDUG.ORG
< http://www.idug.org/lsidug > under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference < http://www.idug.org/lsconf >
information, and much more. If you have not yet signed up for Basic
Membership in IDUG, available at no cost, click on Member Services
< http://www.idug.org/lsms >


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Roger Miller

Re: Undertanding Access Paths
(in response to Robert Catterall)
Even better would be using Visual Explain or OSC to gather all of the relevant
information. You could check with the statistics advisor to see if information
should be provided. If you think it's wrong, then you can use the service
option to build an XML file with the needed information.

Roger

On Fri, 12 Dec 2008 15:21:58 -0000, Leslie Pendlebury-Bowe
<[login to unmask email]> wrote:

>Hi
>
>You might wish to restructure that email . see below.
>
>Leslie
>
>
>
> _____
>
>From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf
>Of Jair Montealegre
>Sent: 12 December 2008 12:25
>To: [login to unmask email]
>Subject: [DB2-L] Undertanding Access Paths
>
>
>
>Hi guys, I am in an optimization group in my company and I have the
>following situation: There's a table that has 123,981 rows with the
>following indexes defined on it. P324.IWFMGPE1 COD_ESTADO 1 A
FEC_SALIDA 2 A
>COD_MSWEMIB 3 A COD_VIAPAGO 4 A COD_DIISOALF 5 A IMP_VALOR 6 D
P324.IWFMGPE2
>COD_NUMBERRE 1 A COD_ESTADO 2 A P324.IWFMGPE3 FEC_SALIDA 1 A
COD_ESTADO 2 A
>P324.IWFMGPEM (CLUSTER, UNIQUE) 100% cluster ratio COD_NUMBER 1 A
Columns
>listed in indexes above have the following cardinality: COD_NUMBER 123891
>COD_ESTADO 10 FEC_SALIDA 35 COD_MSWEMIB 3 COD_VIAPAGO 11
COD_DIISOALF 10
>IMP_VALOR 67427 COD_NUMBERRE 123518 Now, having this cursor: DECLARE
CURSOR1
>CURSOR FOR SELECT COD_ESTADO , FEC_SALIDA , COD_MSWEMIB ,
COD_VIAPAGO ,
>COD_DIISOALF , IMP_VALOR , COD_NUMBER , COD_MSWEMIBR FROM
TWFMGPEM WHERE
>COD_ESTADO IN(40,42,45,20,21) AND FEC_SALIDA = :DCLTWFMGPEM.PEM-
FEC-SALIDA
>ORDER BY COD_ESTADO , FEC_SALIDA , COD_MSWEMIB , COD_VIAPAGO ,
COD_DIISOALF
>The explain data says that the access that cursor performs will be thru
>index IWFMGPE3 using 1 column (I1), List Prefetch and then performs SORT
>because of the use of clause ORDER BY. Why does optimizer choose that
access
>path?. Isn't it better access path to use index IWFMGPE1, avoid performing
>SORT and make an N2 access? Thanks in advance for your help. PS: Our
system
>is z/OS and DB2 V8 for z/OS Jair Montealegre Zorro Madrid -Spain
>[login to unmask email]
>
> _____

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Peter Vanroose

Re: Undertanding Access Paths
(in response to Roger Miller)
Roger Miller <[login to unmask email]> wrote:

> Even better would be using Visual Explain or OSC to gather all of
> the relevant information. You could check with the statistics advisor
> to see if information should be provided.

I thought that the Advisors were not part of the OSC, but only of the
(paying) OE? Or did I miss something in OSC?

-- Peter.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

David Seibert

Re: Undertanding Access Paths
(in response to Peter Vanroose)
Hello Peter.

VE & Stats Advisor (for single query or workloads) are included in
OSC; the other advisors are part of OE - query advisor, index advisor,
access path advisor.

Our friend Pat Bossman has excellent presentations on the matter from
IDUG NA & IOD this year.
Get your hands on them if you can.

Dave


The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Peter Vanroose
Sent: Saturday, December 13, 2008 7:36 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Undertanding Access Paths

Roger Miller <[login to unmask email]> wrote:

> Even better would be using Visual Explain or OSC to gather all of the
> relevant information. You could check with the statistics advisor to
> see if information should be provided.

I thought that the Advisors were not part of the OSC, but only of the
(paying) OE? Or did I miss something in OSC?

-- Peter.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Peter Backlund

Re: Undertanding Access Paths
(in response to David Seibert)
OSC (which is free) has the statistics advisor

OE (which is not free) adds index and query advisors

Peter

Peter Vanroose wrote: Roger Miller <[login to unmask email]> wrote: Even better would be using Visual Explain or OSC to gather all of the relevant information. You could check with the statistics advisor to see if information should be provided. I thought that the Advisors were not part of the OSC, but only of the (paying) OE? Or did I miss something in OSC? -- Peter. ______________________________________________________________________ * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA * ______________________________________________________________________ The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.176 / Virus Database: 270.9.17/1846 - Release Date: 2008-12-12 18:59
-- Attend IDUG 2009, North America -- 11-15 May in Denver, Colorado Attend IDUG 2009, Europe -- 5- 9 October in Rome, Italy Learn more at http://www.idug.org +-------------------------------+---------------------------------+ | Peter G Backlund | Telephone: 08 - 38 69 08 | | Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 | | Rasundavagen 94 | Country Code (Sweden): 46 | | S-169 57 SOLNA | Skype: BacklundDB2 | | Sweden | E-mail: [login to unmask email] | +-------------------------------+---------------------------------+ | IBM Database GOLD Consultant | +-------------------------------+---------------------------------+ | Working with DB2 since 1981 ... and forever! | +-----------------------------------------------------------------+

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services

SUBSCRIBE DB2-L Muthuraj

Re: Undertanding Access Paths
(in response to Peter Backlund)
From my experience, DB2 Optimizer always tries to use the index in favor of
some predicate which is restricting the more data retrieval tha Sorting.

I guess that applies here also.

Regards,
Muthu

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Jair Montealegre

Re: Undertanding Access Paths
(in response to SUBSCRIBE DB2-L Muthuraj)
Hi, sorry for the format of message, but it wasn't in that way when I wrote
the original one, I hope this time will be better.

Muthu, the filter factor Is not the same in both indexes?, I mean the number
of qualifying RID's will be the same using IWFMGPE1 or IWFMGPE3, isn't?

Thanks

Jair
---------------------------------------------------------------------------
Hi guys, I am in an optimization group in my company and I have the
following situation:

There’s a table that has 123,981 rows with the following indexes defined on it.

P324.IWFMGPE1
COD_ESTADO 1 A
FEC_SALIDA 2 A
COD_MSWEMIB 3 A
COD_VIAPAGO 4 A
COD_DIISOALF 5 A
IMP_VALOR 6 D

P324.IWFMGPE2

COD_NUMBERRE 1 A
COD_ESTADO 2 A

P324.IWFMGPE3
FEC_SALIDA 1 A
COD_ESTADO 2 A

P324.IWFMGPEM (CLUSTER, UNIQUE)
COD_NUMBER 1 A

Columns listed in indexes above have the following cardinality:

COD_NUMBER 123891
COD_ESTADO 10
FEC_SALIDA 35
COD_MSWEMIB 3
COD_VIAPAGO 11
COD_DIISOALF 10
IMP_VALOR 67427
COD_NUMBERRE 123518

Now, having this cursor:

DECLARE CURSOR1 CURSOR FOR
SELECT COD_ESTADO
, FEC_SALIDA
, COD_MSWEMIB
, COD_VIAPAGO
, COD_DIISOALF
, IMP_VALOR
, COD_NUMBER
, COD_MSWEMIBR
FROM TWFMGPEM
WHERE COD_ESTADO IN(40,42,45,20,21)

AND FEC_SALIDA = :DCLTWFMGPEM.PEM-FEC-SALIDA
ORDER BY COD_ESTADO
, FEC_SALIDA
, COD_MSWEMIB
, COD_VIAPAGO
, COD_DIISOALF
The explain data says that the access that cursor performs will be thru
index IWFMGPE3 using 1 column (I1),
List Prefetch and then performs SORT because of the use of clause ORDER BY.

Why does optimizer choose that access path?.
Isn’t it better access path to use index IWFMGPE1, avoid performing SORT and
make an N2 access?



Thanks in advance for your help.

PS: Our system is z/OS and DB2 V8 for z/OS

Jair Montealegre Zorro
Madrid -Spain

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Peter Vanroose

Re: Undertanding Access Paths
(in response to Jair Montealegre)
Jair Montealegre <[login to unmask email]> wrote:
> ...
> P324.IWFMGPE1
> COD_ESTADO 1 A
> ...
> P324.IWFMGPE3
> FEC_SALIDA 1 A
> COD_ESTADO 2 A
> P324.IWFMGPEM (CLUSTER, UNIQUE)
> COD_NUMBER 1 A
> ...
> WHERE COD_ESTADO IN(40,42,45,20,21)
> AND FEC_SALIDA = :DCLTWFMGPEM.PEM-FEC-SALIDA
> ORDER BY COD_ESTADO, ...


>Isn’t it better access path to use index IWFMGPE1, avoid performing
> SO RT and make an N2 access?

No, a SORT cannot be avoided, even not with Index IWFMGPE1, since it's not
the CLUSTER index, so a list prefetch (which requires a RID SORT) followed
by an additional SORT (for your ORDER BY) would be needed.
Make that index the CLUSTER index and you'll probably get what you expected.

Or just ask for the columns in the index, and again you'll get what you want
since an INDEXONLY access does evidently not need list prefetch!

-- Peter Vanroose,
ABIS Training & Consulting
(Leuven, Belgium)

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

SUBSCRIBE DB2-L Muthuraj

Re: Undertanding Access Paths
(in response to Peter Vanroose)
No... its not.. MATCHCOL should be 1 If its going to use the first index rather
than second index as IN predicate is used.

Regards,
Muthu

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Terry Purcell

Re: Undertanding Access Paths
(in response to SUBSCRIBE DB2-L Muthuraj)
Jair,

IWFMGPE1 could be used by optimizer to access the data and avoid an ORDER
BY sort.

I am guessing that the clusterratio of IWFMGPE1 and IWFMGPE3 are relatively
low. If so, then optimizer would expect that retrieving all qualified data rows
via IWFMGPE1 would incur significant random I/O.

If random I/O is the dominant cost for the access path, then it is not
surprising that an access path with list prefetch is chosen. But unfortunately
with list prefetch, data is processed in RID order, and thus key order is lost.
Which requires a final ORDER BY sort.

With regard to the index chosen, IWFMGPE1 cannot be used as matching with
list prefetch, because it would require IN-list access on the leading column.
And IN-list (ACCESSTYPE='N') and list prefetch are mutually exclusive.
Although IWFMGPE3 can be matching on the leading equal, and screening on
the IN wth list prefetch.

If you are satisfied that the optimizer is not choosing the right access path,
then adding OPTIMIZE FOR 1 ROW (as previously suggested by another reply)
will force a sort avoidance plan.

Regards
Terry Purcell

On Mon, 15 Dec 2008 08:34:59 +0000, Jair Montealegre
<[login to unmask email]> wrote:

>Hi, sorry for the format of message, but it wasn't in that way when I wrote
>the original one, I hope this time will be better.
>
>Muthu, the filter factor Is not the same in both indexes?, I mean the number
>of qualifying RID's will be the same using IWFMGPE1 or IWFMGPE3, isn't?
>
>Thanks
>
>Jair
>--------------------------------------------------------------------------
-
>Hi guys, I am in an optimization group in my company and I have the
>following situation:
>
>There’s a table that has 123,981 rows with the following indexes defined on
it.
>
>P324.IWFMGPE1
> COD_ESTADO 1 A
> FEC_SALIDA 2 A
> COD_MSWEMIB 3 A
> COD_VIAPAGO 4 A
> COD_DIISOALF 5 A
> IMP_VALOR 6 D
>
> P324.IWFMGPE2
>
> COD_NUMBERRE 1 A
> COD_ESTADO 2 A
>
> P324.IWFMGPE3
> FEC_SALIDA 1 A
> COD_ESTADO 2 A
>
> P324.IWFMGPEM (CLUSTER, UNIQUE)
> COD_NUMBER 1 A
>
>Columns listed in indexes above have the following cardinality:
>
>COD_NUMBER 123891
>COD_ESTADO 10
>FEC_SALIDA 35
>COD_MSWEMIB 3
>COD_VIAPAGO 11
>COD_DIISOALF 10
>IMP_VALOR 67427
>COD_NUMBERRE 123518
>
>Now, having this cursor:
>
>DECLARE CURSOR1 CURSOR FOR
> SELECT COD_ESTADO
> , FEC_SALIDA
> , COD_MSWEMIB
> , COD_VIAPAGO
> , COD_DIISOALF
> , IMP_VALOR
> , COD_NUMBER
> , COD_MSWEMIBR
> FROM TWFMGPEM
> WHERE COD_ESTADO IN(40,42,45,20,21)
>
> AND FEC_SALIDA = :DCLTWFMGPEM.PEM-FEC-SALIDA
> ORDER BY COD_ESTADO
> , FEC_SALIDA
> , COD_MSWEMIB
> , COD_VIAPAGO
> , COD_DIISOALF
>The explain data says that the access that cursor performs will be thru
>index IWFMGPE3 using 1 column (I1),
>List Prefetch and then performs SORT because of the use of clause ORDER
BY.
>
>Why does optimizer choose that access path?.
>Isn’t it better access path to use index IWFMGPE1, avoid performing SORT
and
>make an N2 access?
>
>
>
>Thanks in advance for your help.
>
>PS: Our system is z/OS and DB2 V8 for z/OS
>
>Jair Montealegre Zorro
>Madrid -Spain

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms