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

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.