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

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.