Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed.freenet.de!news.n-ix.net!news.belwue.de!news.uni-stuttgart.de!carbon.eu.sun.com!btnet-feed5!btnet!news.btopenworld.com!not-for-mail From: "Jonathan Lewis" Newsgroups: comp.databases.oracle.server Subject: Re: ROWNUM & server performance Date: Fri, 11 Nov 2005 17:21:38 +0000 (UTC) Organization: BT Openworld Lines: 88 Message-ID: References: <1131722932.356902.262950@g47g2000cwa.googlegroups.com> NNTP-Posting-Host: host86-131-1-59.range86-131.btcentralplus.com X-Trace: nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com 1131729698 6009 86.131.1.59 (11 Nov 2005 17:21:38 GMT) X-Complaints-To: news-complaints@lists.btinternet.com NNTP-Posting-Date: Fri, 11 Nov 2005 17:21:38 +0000 (UTC) X-RFC2646: Format=Flowed; Original X-Newsreader: Microsoft Outlook Express 6.00.2900.2180 X-MSMail-Priority: Normal X-Priority: 3 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:255239 "Patrick Demets" wrote in message news: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" wrote in message > news:1131722932.356902.262950@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