# Interesting Optimizer Problem

#### Fred Edgar

Interesting Optimizer Problem
There have been some good points made already. Here's something else you
could try instead of using the MIN function:

Select LOAN_ID from LOAN_TAB
Where CUST_ID = :HV1
And LOAN_ID > :HV2
ORDER BY CUST_ID
, LOAN_ID
FETCH FIRST 1 ROW ONLY;

The optimizer seems to get stranger all the time. Some things are better and
some like this just don't seem to make sense. It's intuitive to us what the
access path should be, but it's not always easy to code common sense.

Fred

> Table: LOAN_TAB 3,005 rows
> LOAN_ID dec(9) cardinality 2,721
> CUST_ID int cardinality 2,425
> COLX char(20)
> Etcâ€¦.
>
>
>
> The table LOAN_TAB has two Indexes both non-unique. Different tests were
> run with clustering on each index:
>
>
> Index 1:
> LOAN_ID asc
> CUST_ID asc
>
> Index 2:
> CUST_ID asc
> LOAN_ID asc
>
> The SQL:
>
> Select min(LOAN_ID) from LOAN_TAB
> Where CUST_ID = :HV1
> And LOAN_ID > :HV2 ;
>
> The optimizer invariably chooses Index 1 for this SQL. If it would use Index
> 2
> it would find only one or two rows matching the CUST_ID. Using Index 1 it
> must go through many LOAN_ID's until it finds a match on CUST_ID. And we
> are clearly getting very inefficient results with Index 1. Why doesn't the
> optimizer choose Index 2??
>
> My understanding is that "LOAN_ID >" has a filter factor of .3
> whereas "CUST_ID=" has a filter factor of about .0004, making "CUST_ID="
> the preferred predicate.
>
> Who can explain this?

____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the 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

#### Gerald Hodge

Re: Interesting Optimizer Problem
(in response to Fred Edgar)
Just as a matter of curiosity, have you considered doing a hint and seeing if there is a performance difference? Where we have seen this before the Optimizer has made the correct choice. The issue is there is no exegesis of the optimizer's choices.

Gerald Hodge
HLS Technologies, Inc.
www.hlstechnologies.com

> Table: LOAN_TAB 3,005 rows
> LOAN_ID dec(9) cardinality 2,721
> CUST_ID int cardinality 2,425
> COLX char(20)
> Etcâ€¦.
>
>
>
> The table LOAN_TAB has two Indexes both non-unique. Different tests were
> run with clustering on each index:
>
>
> Index 1:
> LOAN_ID asc
> CUST_ID asc
>
> Index 2:
> CUST_ID asc
> LOAN_ID asc
>
> The SQL:
>
> Select min(LOAN_ID) from LOAN_TAB
> Where CUST_ID = :HV1
> And LOAN_ID > :HV2 ;
>
> The optimizer invariably chooses Index 1 for this SQL. If it would use Index
> 2
> it would find only one or two rows matching the CUST_ID. Using Index 1 it
> must go through many LOAN_ID's until it finds a match on CUST_ID. And we
> are clearly getting very inefficient results with Index 1. Why doesn't the
> optimizer choose Index 2??
>
> My understanding is that "LOAN_ID >" has a filter factor of .3
> whereas "CUST_ID=" has a filter factor of about .0004, making "CUST_ID="
> the preferred predicate.
>
> Who can explain this?

____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the 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.
Version: 7.5.516 / Virus Database: 269.17.13/1206 - Release Date: 1/1/2008 12:09 PM

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the 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

#### Avram Friedman

Re: Interesting Optimizer Problem
(in response to Gerald Hodge)
Have you looked at your RUNSTATS control statements?
RUNSTATS needs some encouragement to collect data on anything other than
the first column. I think something like the following works

RUNSTATS TABLESPACE DDDDDDDD.SSSSSSSS
TABLE(DDDDDDDD.TTTTTTTT)
INDEX(ALL KEYCARD)
FREQVAL 1 COUNT 150
FREQVAL 2 COUNT 200
FREQVAL 3 COUNT 100
SORTDEVT SYSDA
SHRLEVEL CHANGE REPORT YES

Regards
Avram Friedman

>
>> Table: LOAN_TAB 3,005 rows
>> LOAN_ID dec(9) cardinality 2,721
>> CUST_ID int cardinality 2,425
>> COLX char(20)
>> Etc….
>>
>>
>>
>> The table LOAN_TAB has two Indexes both non-unique. Different tests
were
>> run with clustering on each index:
>>
>>
>> Index 1:
>> LOAN_ID asc
>> CUST_ID asc
>>
>> Index 2:
>> CUST_ID asc
>> LOAN_ID asc
>>
>> The SQL:
>>
>> Select min(LOAN_ID) from LOAN_TAB
>> Where CUST_ID = :HV1
>> And LOAN_ID > :HV2 ;
>>
>> The optimizer invariably chooses Index 1 for this SQL. If it would use Index
>> 2
>> it would find only one or two rows matching the CUST_ID. Using Index 1 it
>> must go through many LOAN_ID's until it finds a match on CUST_ID. And we
>> are clearly getting very inefficient results with Index 1. Why doesn't the
>> optimizer choose Index 2??
>>
>> My understanding is that "LOAN_ID >" has a filter factor of .3
>> whereas "CUST_ID=" has a filter factor of about .0004, making "CUST_ID="
>> the preferred predicate.
>>
>> Who can explain this?
>
>
>
>
__________________________________________________________________
__________________
>Be a better friend, newshound, and
>know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
archives, the 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

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the 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

#### Lance D Ziegler1

Re: Interesting Optimizer Problem
(in response to Avram Friedman)

Please check APAR II13579 - DB2 PERFORMANCE AND TUNING INFORMATIONAL APAR.  It contains several points of advice regarding optimizer issues, including guidance on the appropriate level of RUNSTATS.
American Express made the following annotations on Wed Jan 02 2008 10:59:38 ------------------------------------------------------------------------------ "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." American Express a ajouté le commentaire suivant le Wed Jan 02 2008 10:59:38 Ce courrier et toute pièce jointe qu'il contient sont réservés au seul destinataire indiqué et peuvent renfermer des renseignements confidentiels et privilégiés. Si vous n'êtes pas le destinataire prévu, toute divulgation, duplication, utilisation ou distribution du courrier ou de toute pièce jointe est interdite. Si vous avez reçu cette communication par erreur, veuillez nous en aviser par courrier et détruire immédiatement le courrier et les pièces jointes. Merci. ****************************************************************************** -------------------------------------------------------------------------------
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.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