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

Hi

My IBM contact says this: "Assuming it is from the explain of the statement shown, it looks like a bug to me. "

He is very experienced with the SQE support. So I think it was good you put in a support request.

Do you have all the stuff they told you? I and others might be able to help understand it.

Also, is there anything in the outline on the right of the visual explain result that lists the advised index?

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.