Re: Does Oracle allow configuring - number of records returned?

"Patel" <sajidpatel_at_rediffmail.com> wrote:
> Consider this case:
>
> I have an application with oracle database in backend. The Frontend
> allows users to query data based on some criteria. The data retrieved is
> displayed on the screen. The application limits the number of records
> queried to 5000.There could be millions of records in the database.
>
> I was trying to understand, from performance aspect - oracle will always
> return millions of records, but only 5000 are required. Is there any way
> to limit ?

Oracle only returns more records when the client asks for more of them. If your client stops calling "fetch" after 5000 records, Oracle will stop returning records. How this affects performance depends on the execution plan. For sorts, and to a lesser extent hash joins, it may have to do a lot of the work before the first records is available, so stopping the fetching early doesn't make a proportional reduction in workload. Nested loops, on the other hand, are a "pay as you go" procedure, so if you stop retrieving after 1% of rows are in hand, you save about 99% of the workload.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB