Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: query slower using order by asc (default index order) than oder by desc

Re: query slower using order by asc (default index order) than oder by desc

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 17 Feb 2006 11:16:36 +0000 (UTC)
Message-ID: <dt4bak$h8i$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com>

"VIC" <victorcy_at_gmail.com> wrote in message news:1140080207.714101.5710_at_g44g2000cwa.googlegroups.com...
> Scenario:
> Got a big normal(non-partition) table (about 5 million rows), only
> index is the default ascending primary key index on primary key column
> (id)
>
> Query 1.
> select * from contentowner2.system_log order by id asc
>
> Query 2.
> select * from contentowner2.system_log order by id desc
>
> Query 1 will take take more than 10 minutes to run
> Query 2 will take no more than a second to return result.
>
> Could anyone explain to me why query order by asc is so slow compared
> with query order by desc
>
> Thanks,
> Vic
>

According to the execution plans in your later post, the optimizer thinks you have 4 million rows in the table - and you certainly wouldn't get all of them back in "no more than a second".

So -

    how wrong is the optimizer ? Are there     really only a few undeleted rows in the table ?

    When you query the data, are the times you     report the time taken to get "the first few rows".

If I make a few guesses about what your data represents, it is possible that you have a table that is a FIFO queue, with a lot of deletions at the low values for ID which have not yet been cleaned from the index (possibly after a very large sweep deleting millions of rows). In this case, Oracle may have to walk through a lot of empty (or near-empty) index leaf blocks to collect the first few rows that still exist when going bottom up; but if you keep adding new data with higher ids, then it may only take a single leaf block when working top down, to find a full array of data.

You may need to think about a one-off rebuild for this index followed by a regular coalesce if my guesses are correct.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Received on Fri Feb 17 2006 - 05:16:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US