Re: Index advisor in Navigator - why is it asking for DESC sequence?

that's a winner!

I was indeed 1 service pack behind, and when I put on SI53809,
I now get "ASC" on my SQL to create my index...



On 1/16/2015 8:13 AM, Vernon Hamberg wrote:

I got word back from my friend at IBM - he thinks this is a known issue
fixed in service pack 11 for IBM i Access - the actual PTF for it is
SI53809.

Here is more info on how indexes are used by the optimizer - it confirms
what I thought, with additional info -

**********************************************************************************************

The runtime code can go both directions in the index and so it does not
matter if the key is asc/desc, except for a case where I actually need a
mixture of order.
Order by col1 asc, col2 desc Needs an index to be Col1 asc, col2 desc
Or, I suppose it could use and index col1 desc, col2 asc

If I want
Order by col1 desc, col2 desc this could use an index with col1 asc,
col2 asc and read backwards.
**********************************************************************************************


One good thing about this - we don't need to create indexes in both
directions - only one direction is needed, so we can avoid taking the
extra space.

HTH
Vern

On 1/15/2015 1:16 PM, Gqcy wrote:

I put a support case in with IBM on this...

I am not understanding much of what they conveyed to me, but here goes:

1) currently - all index values are stored in ascending order.
2) actual "ordering" is done at time of actual query.


On 1/15/2015 8:19 AM, Gqcy wrote:

yes, that is the index advice for that statement.

when I actually go into run sql scripts, and paste that statement in...
(swap the question mark out for a timestamp value)
and then under Visual explain say "create index".... I see
on the Key tab, it shows under the order column "ascending"...
however, I then click on the "show SQL" button...
and it shows...... DESCending.




On 1/14/2015 5:32 PM, Vernon Hamberg wrote:

LOL - there go all my theories!

I think I'll run this by my IBM friend and see what's up.

And just to be anal about this - is that index advice attached to that
statement?

Luis asks an interesting kind of question, seems to me - maybe the
value
for SFHPSTMP was nearer the end - but I'm not sure that would influence
things.

Have you had a chance to get a Visual Explain result on this? That
usually has some of the advice listed - maybe, IIRC, with reasons.

Curiouser and curiouser!!

Vern

On 1/14/2015 4:46 PM, Gqcy wrote:

ok....
now I have a good example
SQL Plan Cache statements shows:

SELECT * FROM sfplib.sfpbhkspf WHERE sfhptstmp = ? ORDER BY sfhbid
ASC;


however,

CREATE INDEX SFPLIB.SFPBHKSPF_INDEX_00001 ON SFPLIB.SFPBHKSPF
(SFHPTSTMP DESC, SFHBID DESC);




On 1/14/2015 2:54 PM, John Yeung wrote:

On Wed, Jan 14, 2015 at 3:05 PM, Vernon Hamberg
<vhamberg@xxxxxxxxxxxxxxx> wrote:

Strictly speaking, the optimizer can use an ASC key to return rows
in DESC
order pretty efficiently - just start at the end and go backwards.
Like
SETGT and READPE using an ASCending key LF.

This is exactly why OP is asking why DESC is being called for. If
ascending and descending are close enough to equivalent, why prefer
DESC?

Also, from OP's apparent level of surprise, I would guess he either
doesn't have anything that explicitly asks for descending order,
or he
has a mix of ascending and descending.

John Y.