Re: Composite index and data distribution

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 11 Aug 2006 11:59:01 +0100
Message-ID: <Duidnfb-PL1p_0HZRVnyiw@bt.com>

"Michal Kuratczyk" <kura_at_lj.pl> wrote in message news:ebhmcl$nja$1_at_mx1.internetia.pl...
>
> In the first case there were 30 consistent gets and in the second - 2624!
> Why?! Plan is the same. The index contains exactly the answer to the query
> (both columns, sorted as requested), so I would expect it to just grab the
> first 25 rows from the index and return them. I would ignore a few blocks
> difference but it's not the case - Oracle is obviously doing something
> different with those two queries.
>

By giving rownum the alias rn, you've forced Oracle to instantiate the view. ROWNUM is evaluated before the "order by" is applied - that's the rule with ROWNUM, and you can't work around it. So Oracle has to acquire all the rows before "sorting" - so it can't push the predicate inside the order by, even though it manages to recognise that the sort is redundant.

If the following query was logically the equivalent of what you intended, you would find that Oracle could return the data set in 4 LIOs

(Your first_rows(25) hint is redundant in 10g, the optimizer infers it from the rownum <= 25 clause).

select

foo, rownum
from (

        select foo
        from foobar
        where foo=99
        order by bar

)
where rownum <= 25;
;

-- 
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 Aug 11 2006 - 05:59:01 CDT