RE: Advised index not used but still advised

SQE does not time out, CQE may.

Sort Sequence differences may play into this scenario.

In case it's an IBM bug, submit a PMR to IBM. They won't correct the advice
engine unless they hear from the customers on this issue.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com

-----Original Message-----
Subject: Re: Advised index not used but still advised

Rick,

Are there lots of indexes/logical files? The SQE may time out and not
look at every possible index.

Are there Select/Omit logicals? What's the QAQQINI setting for
IGNORE_DERIVED_INDEXES?

Charles

On Mon, Sep 14, 2009 at 10:17 AM, <Rick.Chevalier@xxxxxxxxxxxxxxx> wrote:

Last week I created seven indexes over one of our tables that were advised

by the index advisor.  I got the information from system table SYSIXADV and
built the indexes with the key columns advised and installed them to
production Friday 9/12.  Querying SYSIXADV this morning it shows a last
advised date of Saturday 9/13.  I have compared the advised key columns and
they match exactly to the indexes I installed on Friday.  The indexes were
created with SQL so the advisor should be happy about that.

I don't have access to the query because it originates outside of the

iSeries from our data warehouse group.  The same query is run every night.
 The index is requested for record selection.  I know there is no guarantee
that the optimizer will use the index.  I am wondering what would cause the
system to keep advising these indexes when they already exist?

Rick Chevalier
IT Software Solutions - Loan Servicing
[http://www.americredit.com/images/acf_email_sig.gif]
817-525-7178 (w)
rick.chevalier@xxxxxxxxxxxxxxx<mailto:rick.chevalier@xxxxxxxxxxxxxxx>