Re: ROWNUM & server performance

"Patrick Demets" <notquiteclapton_at_HATESPAMshaw.ca> wrote in message news:aH3df.484456$1i.235762_at_pd7tw2no...
> Thanks for the input Mark. I ran Explain Plan and got this:
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Hint=CHOOSE 99 4
> COUNT STOPKEY
> INDEX FAST FULL SCAN EW_EQMT_ASGN_IX01 1 M 23 M 4
>
> To my way of reading this (not saying it's correct), it looks like the
> index
> is read (for 1M rows). But judging by the cost (4), I'd guess that it
> stops
> when it satisfies the ROWNUM condition.
>
> Thanks
>
>
>
>
> "Mark D Powell" <Mark.Powell_at_eds.com> wrote in message
> news:1131722932.356902.262950_at_g47g2000cwa.googlegroups.com...
>> Patrick, run an explain plan on the SQL statement. You will probably
>> see a reference to something called "count stopkey".
>>
>> > EXPLAIN PLAN SET statement_id = 'mpowel01' FOR
>> 2 select item_no from item_master where rownum < 100
>> 3 /
>>
>> Explained.
>>
>> > rem
>> > rem
>> > set echo off
>>
>> QUERY_PLAN
>> ----------------------------------------------------------------------
>> COST CARDINALITY
>> ---------- -----------
>> SELECT STATEMENT
>> 98 99
>> 2.1 COUNT STOPKEY
>> 3.1 INDEX FAST FULL SCAN ITEM_MASTER_PRIME UNIQUE
>> 98 459991
>>
>> How Oracle performs the task will depend on the CBO but you can extect
>> Oracle to stop once it has enough data.
>>
>> HTH -- Mark D Powell --
>>
>
>

There are several places where the numbers reported in explain plan are not entirely consistent. In this case, the optimizer and run-time engine are able, and smart enough, to stop after the minimum amount of work. It just happens that the plan reports the volume of data that would have been processed if the entire scan had to run.

Bear in mind that in the general case, the scan may have to continue through a lot of data to find the few rows that might match your
WHERE clause - so the plan shows a
generic result, which isn't necessarily a true indication of what happens at run time.

-- 
Regards

Jonathan Lewis

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

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005