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

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

I would guess that you had a SELECT statement that used DESC - if the 
columns listed could be dates or times and you want  to see most recent, 
this could happen.

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.

Vern

On 1/14/2015 8:51 AM, Gqcy wrote:
> I am starting to look at the big hitter items on my Index advisor...
> it shows the keys advised, but when I say "show SQL", they _always_ 
> show to create the index DESC on the fields.
>
> I haven't been doing that.... and it appears to no longer advise a 
> index be created.   but is there a performance or database structure
> reason I should be creating the indexes as DESCending?
>
>
> example:
>
> /* Creating index XXDATA.DTH_INDEX_00001
> When creating this index the database connection should have a sort 
> sequence of *HEX.
>
> -- System name: Kxxx.xxxxxxxx.com
> -- Database: Sxxxxxxd         Advised Indexes for Sxxxxxxd
> -- Times Advised for Query Use: 2586259
> -- First Advised: 4/1/13 8:14:05 AM
> -- Last Query Use: 1/14/15 8:26:52 AM
> -- Reason Advised: Row selection
> -- Estimated Index Creation Time (seconds): 00:00:20
> -- Rows in Table when Advised: 569296
> -- Average of Query Estimates (seconds): 0.0001
> -- Most Expensive Query Estimate (seconds): 1
> -- Index Type Advised: Not unique */
> CREATE INDEX XXDATA.DTH_INDEX_00001 ON XXDATA.DTH (DTNO  DESC, LPROD  
> DESC);
>
> /* Setting label text for XXDATA.DTH_INDEX_00001 */
> LABEL ON INDEX XXDATA.DTH_INDEX_00001 IS 'Index generated from Index 
> Advisor';