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

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Sat, 15 May 2004 14:05:01 +0200
Message-ID: <c850qc$ojm$1@news5.tilbu1.nb.home.nl>

Hans Forbrich wrote:

> Patel 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 ?
>>
>>Ofcourse where clause would be used to filter the results, still the
>>result could be huge number of records.

>
>
> Please forgive the jumping in ...

Absolution.
>
> This is a very reasonable question - Oracle's tool sets (such as Forms) uses
> some variants of the solutions that are available. There are several ways
> of accomplishing this. The answer depends on several things:
>
> - version of Oracle database
> - operating system of Oracle database
> - client (where the SQL runs) language
> - client operating system
> - your programming model
>

Right - and with the limited infromation given, it could have been any of the above.

Adding to the possibilities: what about a web site? I'd surely like to see the amount of data on a page limited (if only to reduce waiting time, or cost, right, Howard?).

> You would find a very decent discussion tuned to the Oracle8i environment in
> Thomas Kyte's Expert One on One Oracle book.
>
> An alternate source is at http://docs.oracle.com - look for the "Developer's
> Guide" for the database version in question.
>
> /Hans

One -peromance killing- possibility is to display the number of records found, and decide from there on; e.g. if more than 6,0000 ask user to be more restrictive.
Combine that with techniques discussed elsewhere in this thread.

And the rownum trick works on a set:
select * from (...your query here...) where rownum < 40; however, where rownum > 5 will not - imagine why not.

-- 

Regards,
Frank van Bortel

Received on Sat May 15 2004 - 07:05:01 CDT