Undertanding Access Paths

Jair Montealegre

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