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

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';