RE: Restricting # of records processed

You can create a profile where you could limit the number of logical reads per call. This is not the number of records read but the # of logical and physical datablock reads, but you might calculate the average numvber of rows per block and estimate this number.  

HTH.   Djordje

-----Original Message-----
From: Baylis, John [mailto:JBaylis_at_mail.canfor.ca] Sent: November 17, 2000 3:12 PM
To: Multiple recipients of list ORACLE-L Subject: Restricting # of records processed

We have an application that allows users to enter various selection criteria for a query.

Is there any way to programatically stop processing the query after x numbers of records read in order to tell the user his selection criteria is not restrictive enough?

In other words, I may have to read more than 1,000,000 records before it matches my selection. I would like to say to the user 'You have read 100,000 rows so far, ... please be more selective'.

I do not think using rownum will work since rownum is assigned to rows that meet the selection criteria not to all the rows read.

I do not want to use a user profile with logical_reads_per_session specified since I do not want the session to be terminated after reaching that limit.

Is there another way?

John Baylis
DBA / Systems Administrator
Canadian Forest Products Ltd. Received on Fri Nov 17 2000 - 15:29:13 CST