Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Is it possible to get a subset of a result set?

Re: Is it possible to get a subset of a result set?

From: Steven Hill <java410_at_yahoo.com>
Date: Thu, 23 Sep 1999 12:52:31 -0400
Message-ID: <37EA5ACF.BB7B1144@yahoo.com>


I ran your query listed below. It whirled and buzzed for several minutes until barfing up ORA-01652: unable to extend temp segment by 27309 in tablespace TEMPORARY_DATA.

Even if it did run successfully, the query needs to be very fast (several seconds) to be useful in the context I'm need it for.

Thanks.
Steve

Jay Weiland wrote:

> Hey Steven,
>
> Try the following to time the query and it will give you the time it took to execute. I would be interested to see how long it took on your system. You can also take a look at the foo variable to see if you are getting the correct number of results.
>
> Jay!!!
>
> DECLARE
> time_before BINARY_INTEGER;
> time_after BINARY_INTEGER;
> foo INTEGER;
> BEGIN
> time_before := DBMS_UTILITY.GET_TIME;
>
> SELECT count(*)
> INTO foo
> FROM (select employee, rank
> from (select emp1.emp_name employee, count(*) rank
> from temp_emp emp1,
> temp_emp emp2
> where emp1.emp_name >= emp2.emp_name
> group by emp1.emp_name)
> where rank between 8000 and 8500);
>
> time_after := DBMS_UTILITY.GET_TIME;
> DBMS_OUTPUT.PUT_LINE ((time_after - time_before) / 100 || ' seconds');
> END;
>
> Steven Hill wrote:
>
> > Interesting solution. Any idea how slow is "slow" for a result set, say
> > 10000 rows are returned from the subquery in the from clause, min_rank is
> > 8000 and max_rank is 8500? Assume emp_name is indexed.
> >
> > Steven Hill
> >
Received on Thu Sep 23 1999 - 11:52:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US