Access path and index choice

Adam Baldwin

Access path and index choice
Fellow listers: Can anyone throw some light on this.

We have a query:

SELECT *
FROM MYTAB
WHERE ( CODISER = '11'
AND OFIAPE = '111'
AND NUMECTA = '111111'
AND DIGICTA = '1'
AND ENTIDAD = 1
AND NIF ='X11111111'
AND SUFNIF IN ( '100',' ')
AND REFER IN ( '000030013912',' '))

against a table defined as follows:
CREATE TABLE MYTAB
(CODISER CHAR(2) FOR SBCS DATA NOT NULL WITH DEFAULT
,
OFIAPE CHAR(3) FOR SBCS DATA NOT NULL WITH DEFAULT
,
NUMECTA CHAR(6) FOR SBCS DATA NOT NULL WITH DEFAULT
,
DIGICTA CHAR(1) FOR SBCS DATA NOT NULL WITH DEFAULT
,
ENTIDAD DECIMAL(4, 0) NOT NULL WITH DEFAULT ,
NIF CHAR(9) FOR SBCS DATA NOT NULL WITH DEFAULT
,
SUFNIF CHAR(3) FOR SBCS DATA NOT NULL WITH DEFAULT
,
REFER CHAR(12) FOR SBCS DATA NOT NULL
WITH DEFAULT ,
IMPORTE DECIMAL(13, 2) NOT NULL WITH DEFAULT ,
FEINIDES DATE NOT NULL WITH DEFAULT ,
FEFINDES DATE NOT NULL WITH DEFAULT ,
FEALTA CHAR(6) FOR SBCS DATA NOT NULL WITH DEFAULT
,
FEULMOD CHAR(6) FOR SBCS DATA NOT NULL WITH DEFAULT
,
HORULMOD CHAR(6) FOR SBCS DATA NOT NULL WITH DEFAULT
,
OFIULMOD CHAR(3) FOR SBCS DATA NOT NULL WITH DEFAULT
,
NUMTER CHAR(4) FOR SBCS DATA NOT NULL WITH DEFAULT
,
USUARIO CHAR(4) FOR SBCS DATA NOT NULL WITH DEFAULT
,
A2000E_MNDORIG CHAR(1) FOR SBCS DATA NOT NULL WITH DEFAULT
,
A2000E_EUR_IMP DECIMAL(13, 0) NOT NULL WITH DEFAULT ,
A2000N_FEULMOD DATE WITH DEFAULT NULL ,
A2000N_FEALTA DATE WITH DEFAULT NULL ,
A2000D_IMPORTE DECIMAL(13, 0) NOT NULL WITH DEFAULT ,
A2000_DUMMY VARCHAR(2000) FOR SBCS DATA NOT NULL
WITH DEFAULT )

There are two indexes:

One, the clustering index (unique) on:

(CODISER ASC,
OFIAPE ASC,
NUMECTA ASC,
DIGICTA ASC,
ENTIDAD ASC,
NIF ASC,
SUFNIF ASC,
REFER ASC)

and the other, non unique, on:

NIF ASC,
SUFNIF ASC,
REFER ASC)

The query uses the second index with a non mathcing scan on 2 cols. Stats
are up to date.

Why is the optimizer going for the non clustering index, ignoring 6
matchcols on the clustering index?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Bart Mertens

Re: Access path and index choice
(in response to Adam Baldwin)
Adam,

In order to answer your question we need to know the table/column
statistics. NIF probably has a high filter factor.


-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
Adam Baldwin
Verzonden: donderdag 29 december 2005 10:47
Aan: [login to unmask email]
Onderwerp: [DB2-L] Access path and index choice

Fellow listers: Can anyone throw some light on this.

We have a query:

SELECT *
FROM MYTAB
WHERE ( CODISER = '11'
AND OFIAPE = '111'
AND NUMECTA = '111111'
AND DIGICTA = '1'
AND ENTIDAD = 1
AND NIF ='X11111111'
AND SUFNIF IN ( '100',' ')
AND REFER IN ( '000030013912',' '))

against a table defined as follows:
CREATE TABLE MYTAB
(CODISER CHAR(2) FOR SBCS DATA NOT NULL WITH DEFAULT
,
OFIAPE CHAR(3) FOR SBCS DATA NOT NULL WITH DEFAULT
,
NUMECTA CHAR(6) FOR SBCS DATA NOT NULL WITH DEFAULT
,
DIGICTA CHAR(1) FOR SBCS DATA NOT NULL WITH DEFAULT
,
ENTIDAD DECIMAL(4, 0) NOT NULL WITH DEFAULT ,
NIF CHAR(9) FOR SBCS DATA NOT NULL WITH DEFAULT
,
SUFNIF CHAR(3) FOR SBCS DATA NOT NULL WITH DEFAULT
,
REFER CHAR(12) FOR SBCS DATA NOT NULL
WITH DEFAULT ,
IMPORTE DECIMAL(13, 2) NOT NULL WITH DEFAULT ,
FEINIDES DATE NOT NULL WITH DEFAULT ,
FEFINDES DATE NOT NULL WITH DEFAULT ,
FEALTA CHAR(6) FOR SBCS DATA NOT NULL WITH DEFAULT
,
FEULMOD CHAR(6) FOR SBCS DATA NOT NULL WITH DEFAULT
,
HORULMOD CHAR(6) FOR SBCS DATA NOT NULL WITH DEFAULT
,
OFIULMOD CHAR(3) FOR SBCS DATA NOT NULL WITH DEFAULT
,
NUMTER CHAR(4) FOR SBCS DATA NOT NULL WITH DEFAULT
,
USUARIO CHAR(4) FOR SBCS DATA NOT NULL WITH DEFAULT
,
A2000E_MNDORIG CHAR(1) FOR SBCS DATA NOT NULL WITH DEFAULT
,
A2000E_EUR_IMP DECIMAL(13, 0) NOT NULL WITH DEFAULT ,
A2000N_FEULMOD DATE WITH DEFAULT NULL ,
A2000N_FEALTA DATE WITH DEFAULT NULL ,
A2000D_IMPORTE DECIMAL(13, 0) NOT NULL WITH DEFAULT ,
A2000_DUMMY VARCHAR(2000) FOR SBCS DATA NOT NULL
WITH DEFAULT )

There are two indexes:

One, the clustering index (unique) on:

(CODISER ASC,
OFIAPE ASC,
NUMECTA ASC,
DIGICTA ASC,
ENTIDAD ASC,
NIF ASC,
SUFNIF ASC,
REFER ASC)

and the other, non unique, on:

NIF ASC,
SUFNIF ASC,
REFER ASC)

The query uses the second index with a non mathcing scan on 2 cols.
Stats are up to date.

Why is the optimizer going for the non clustering index, ignoring 6
matchcols on the clustering index?

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Adam Baldwin

Re: Access path and index choice
(in response to Bart Mertens)
Sorry - I forgot the col stats:

NAME COLNO COLCARD
------------------ ------ -----------
CODISER 1 11
OFIAPE 2 236
NUMECTA 3 57344
DIGICTA 4 10
ENTIDAD 5 1792
NIF 6 15178
SUFNIF 7 712
REFER 8 165888
IMPORTE 9 4224
FEINIDES 10 7936
FEFINDES 11 848
FEALTA 12 6208
FEULMOD 13 6208
HORULMOD 14 40960
OFIULMOD 15 296
NUMTER 16 1344
USUARIO 17 2176
A2000E_MNDORIG 18 2
A2000E_EUR_IMP 19 3008
A2000N_FEULMOD 20 5248
A2000N_FEALTA 21 5248
A2000_DUMMY 23 623088

NIF has a high(ish) value but the first three cols of the clustering index
would do better.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Pradeep Gunjala

Re: Access path and index choice
(in response to Adam Baldwin)
Can you check the Cluster ratio of both the indexes.

- Pradeep




Adam Baldwin <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/29/2005 04:46 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
[DB2-L] Access path and index choice






Fellow listers: Can anyone throw some light on this.

We have a query:

SELECT *
FROM MYTAB
WHERE ( CODISER = '11'
AND OFIAPE = '111'
AND NUMECTA = '111111'
AND DIGICTA = '1'
AND ENTIDAD = 1
AND NIF ='X11111111'
AND SUFNIF IN ( '100',' ')
AND REFER IN ( '000030013912',' '))

against a table defined as follows:
CREATE TABLE MYTAB
(CODISER CHAR(2) FOR SBCS DATA NOT NULL WITH DEFAULT
,
OFIAPE CHAR(3) FOR SBCS DATA NOT NULL WITH DEFAULT
,
NUMECTA CHAR(6) FOR SBCS DATA NOT NULL WITH DEFAULT
,
DIGICTA CHAR(1) FOR SBCS DATA NOT NULL WITH DEFAULT
,
ENTIDAD DECIMAL(4, 0) NOT NULL WITH DEFAULT ,
NIF CHAR(9) FOR SBCS DATA NOT NULL WITH DEFAULT
,
SUFNIF CHAR(3) FOR SBCS DATA NOT NULL WITH DEFAULT
,
REFER CHAR(12) FOR SBCS DATA NOT NULL
WITH DEFAULT ,
IMPORTE DECIMAL(13, 2) NOT NULL WITH DEFAULT ,
FEINIDES DATE NOT NULL WITH DEFAULT ,
FEFINDES DATE NOT NULL WITH DEFAULT ,
FEALTA CHAR(6) FOR SBCS DATA NOT NULL WITH DEFAULT
,
FEULMOD CHAR(6) FOR SBCS DATA NOT NULL WITH DEFAULT
,
HORULMOD CHAR(6) FOR SBCS DATA NOT NULL WITH DEFAULT
,
OFIULMOD CHAR(3) FOR SBCS DATA NOT NULL WITH DEFAULT
,
NUMTER CHAR(4) FOR SBCS DATA NOT NULL WITH DEFAULT
,
USUARIO CHAR(4) FOR SBCS DATA NOT NULL WITH DEFAULT
,
A2000E_MNDORIG CHAR(1) FOR SBCS DATA NOT NULL WITH DEFAULT
,
A2000E_EUR_IMP DECIMAL(13, 0) NOT NULL WITH DEFAULT ,
A2000N_FEULMOD DATE WITH DEFAULT NULL ,
A2000N_FEALTA DATE WITH DEFAULT NULL ,
A2000D_IMPORTE DECIMAL(13, 0) NOT NULL WITH DEFAULT ,
A2000_DUMMY VARCHAR(2000) FOR SBCS DATA NOT NULL
WITH DEFAULT )

There are two indexes:

One, the clustering index (unique) on:

(CODISER ASC,
OFIAPE ASC,
NUMECTA ASC,
DIGICTA ASC,
ENTIDAD ASC,
NIF ASC,
SUFNIF ASC,
REFER ASC)

and the other, non unique, on:

NIF ASC,
SUFNIF ASC,
REFER ASC)

The query uses the second index with a non mathcing scan on 2 cols. Stats
are up to date.

Why is the optimizer going for the non clustering index, ignoring 6
matchcols on the clustering index?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Adam Baldwin

Re: Access path and index choice
(in response to Pradeep Gunjala)
99% for the clustering index and 49% for the non clustering.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Larry Kintisch

Re: Access path and index choice
(in response to Adam Baldwin)
Adam,
I don't know what version of DB2 you are using. Older versions didn't
like the "integer value" 1 that you supplied for ENTIDAD that was defined
as DECIMAL (4,0); the index wasn't used. Try the query with "...AND
ENTIDAD = 1. " putting the decimal point in as part of the literal.

If this is a replacement for a query that uses host variables, be sure
that the data types and lengths match as well.

Larry Kintisch
Index design class http://www.DBIndexDesign.com


At 09:32 AM 12/29/2005 -0600, you wrote:
>99% for the clustering index and 49% for the non clustering.
>
>---------------------------------------------------------------------------
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
>
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Adam Baldwin

Re: Access path and index choice
(in response to Larry Kintisch)
We're running V7 on OS/390 02.10.00. The decimal point in the literal made
no difference. Also I received an email reply in relation to PTF PQ97866:
INEFFICIENT INDEX CHOSEN FOR SINGLE-TABLE QUERY WHEN COMPETING
INDEXES HAVE DIFFERENT NLEVELS 05/05/05 PTF PECHANGE. Both indexes have
NLEVEL = 3 so I don't think that this will be it either.

I just can't see why the non-clustering index would give a better access
path.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mark Vickers

Re: Access path and index choice
(in response to Adam Baldwin)
Adam,
I don't have a solution for you, but what about either dropping the NC
index or using opthint or some additional predicates (and NUMECTA =
NUMECTA) to try to influence the optimizer to use the clustering index and
see if you actually do get a better throughput ?




Adam Baldwin <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/30/2005 01:22 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: Access path and index choice






We're running V7 on OS/390 02.10.00. The decimal point in the literal made
no difference. Also I received an email reply in relation to PTF PQ97866:
INEFFICIENT INDEX CHOSEN FOR SINGLE-TABLE QUERY WHEN COMPETING
INDEXES HAVE DIFFERENT NLEVELS 05/05/05 PTF PECHANGE. Both indexes have
NLEVEL = 3 so I don't think that this will be it either.

I just can't see why the non-clustering index would give a better access
path.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Colleen Clow

Re: Access path and index choice
(in response to Mark Vickers)
Maybe there's something about the index itself that makes the second index
a better choice. Have you checked the sysindexepart table? Maybe the
index just needs to be reorganized because it's in a lot of extents or it's
got a high number of the far leaf pages...?

cmc




"Adam Baldwin"
<[login to unmask email] To: [login to unmask email]
COM> cc:
Sent by: "DB2 Subject: Re: [DB2-L] Access path and index choice
Data Base
Discussion List"
<[login to unmask email]
ORG>


12/30/2005 01:22
AM
Please respond to
"DB2 Database
Discussion list
at IDUG"





We're running V7 on OS/390 02.10.00. The decimal point in the literal made
no difference. Also I received an email reply in relation to PTF PQ97866:
INEFFICIENT INDEX CHOSEN FOR SINGLE-TABLE QUERY WHEN COMPETING
INDEXES HAVE DIFFERENT NLEVELS 05/05/05 PTF PECHANGE. Both indexes have
NLEVEL = 3 so I don't think that this will be it either.

I just can't see why the non-clustering index would give a better access
path.

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

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm




**********
The information contained in this communication is confidential, private, proprietary, or otherwise privileged and is intended only for the use of the addressee. Unauthorized use, disclosure, distribution or copying is strictly prohibited and may be unlawful. If you have received this communication in error, please notify the sender immediately at (312)653-6000 in Illinois; (972)766-6900 in Texas; or (800)835-8699 in New Mexico.
**********

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Terry Purcell

Re: Access path and index choice
(in response to Colleen Clow)
It is difficult to provide a definitive answer with such limited detail.
Although with what information is available, and given how V7 makes use of
KEYCARD statistics for these type of predicates (V8 makes more extensive
use of KEYCARD), both indexes will cost out to be very close.

However I do not wish to speculate which V7 zparms or PTFs may alter the
index choice here without more detailed analysis.

A potential workaround is to reorder the columns of the 2nd index so that
REFER becomes a matching rather than screening predicate (or replacing
SUFNIF with another high cardinality column from the WHERE clause). Then
the effect of choosing this index may not be negative.

NIF ASC,
REFER ASC,
SUFNIF ASC)

Regards
Terry Purcell

On Thu, 29 Dec 2005 03:46:33 -0600, Adam Baldwin <[login to unmask email]>
wrote:

>Fellow listers: Can anyone throw some light on this.
>
>We have a query:
>
>SELECT *
>FROM MYTAB
>WHERE ( CODISER = '11'
>AND OFIAPE = '111'
>AND NUMECTA = '111111'
>AND DIGICTA = '1'
>AND ENTIDAD = 1
>AND NIF ='X11111111'
>AND SUFNIF IN ( '100',' ')
>AND REFER IN ( '000030013912',' '))
>
>against a table defined as follows:
> CREATE TABLE MYTAB
> (CODISER CHAR(2) FOR SBCS DATA NOT NULL WITH DEFAULT
>,
> OFIAPE CHAR(3) FOR SBCS DATA NOT NULL WITH DEFAULT
>,
> NUMECTA CHAR(6) FOR SBCS DATA NOT NULL WITH DEFAULT
>,
> DIGICTA CHAR(1) FOR SBCS DATA NOT NULL WITH DEFAULT
>,
> ENTIDAD DECIMAL(4, 0) NOT NULL WITH DEFAULT ,
> NIF CHAR(9) FOR SBCS DATA NOT NULL WITH DEFAULT
>,
> SUFNIF CHAR(3) FOR SBCS DATA NOT NULL WITH DEFAULT
>,
> REFER CHAR(12) FOR SBCS DATA NOT NULL
> WITH DEFAULT ,
> IMPORTE DECIMAL(13, 2) NOT NULL WITH DEFAULT ,
> FEINIDES DATE NOT NULL WITH DEFAULT ,
> FEFINDES DATE NOT NULL WITH DEFAULT ,
> FEALTA CHAR(6) FOR SBCS DATA NOT NULL WITH DEFAULT
>,
> FEULMOD CHAR(6) FOR SBCS DATA NOT NULL WITH DEFAULT
>,
> HORULMOD CHAR(6) FOR SBCS DATA NOT NULL WITH DEFAULT
>,
> OFIULMOD CHAR(3) FOR SBCS DATA NOT NULL WITH DEFAULT
>,
> NUMTER CHAR(4) FOR SBCS DATA NOT NULL WITH DEFAULT
>,
> USUARIO CHAR(4) FOR SBCS DATA NOT NULL WITH DEFAULT
>,
> A2000E_MNDORIG CHAR(1) FOR SBCS DATA NOT NULL WITH DEFAULT
>,
> A2000E_EUR_IMP DECIMAL(13, 0) NOT NULL WITH DEFAULT ,
> A2000N_FEULMOD DATE WITH DEFAULT NULL ,
> A2000N_FEALTA DATE WITH DEFAULT NULL ,
> A2000D_IMPORTE DECIMAL(13, 0) NOT NULL WITH DEFAULT ,
> A2000_DUMMY VARCHAR(2000) FOR SBCS DATA NOT NULL
> WITH DEFAULT )
>
>There are two indexes:
>
>One, the clustering index (unique) on:
>
> (CODISER ASC,
> OFIAPE ASC,
> NUMECTA ASC,
> DIGICTA ASC,
> ENTIDAD ASC,
> NIF ASC,
> SUFNIF ASC,
> REFER ASC)
>
>and the other, non unique, on:
>
> NIF ASC,
> SUFNIF ASC,
> REFER ASC)
>
>The query uses the second index with a non mathcing scan on 2 cols. Stats
>are up to date.
>
>Why is the optimizer going for the non clustering index, ignoring 6
>matchcols on the clustering index?
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm