Re: ROWNUM & server performance

From: Patrick Demets <notquiteclapton_at_HATESPAMshaw.ca>
Date: Fri, 11 Nov 2005 16:31:02 GMT
Message-ID: <aH3df.484456$1i.235762@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 --
>
Received on Fri Nov 11 2005 - 10:31:02 CST