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

Home -> Community -> Usenet -> c.d.o.server -> Re: suggestion to improve a query

Re: suggestion to improve a query

From: Keith Boulton <boulke_at_globalnet.co.uk>
Date: 2000/03/27
Message-ID: <38deec9f.1160012@read.news.globalnet.co.uk>#1/1

On Sun, 26 Mar 2000 10:31:27 GMT, boulke_at_globalnet.co.uk (Keith Boulton) wrote:
>I believe the fastest way to do this (i.e. get the first row for each
>customer following a given date) is to use a stored procedure to
>populate a temporary table. The pseudocode is something like
>
>create table temp with structure required.
>
>for each row in ( select * from table order by customer, date where
>date > <input date>)
> on change of customer
> insert into temp values ....
>end loop
>
>Then select from the temporary table. This process requires only a
>single scan of the input table, followed by a scan of the temp table.
>In oracle 8 you can use pl/sql bulk processing to insert a block of
>records at a time into the temp table to improve performance.
>
>If I have time, I will look at implementing this.
>
>Also, your time to fetch the data from the server to vb is way too
>long.
>

I was wrong. The quickest way I've found so far is to add a first rows hint to the previous query. This runs in 203 seconds:

declare

	cursor c is
		select /*+ first_rows */ * from kundeWdatum a
		where ( a.kunde_nr, a.kunde_datum ) in
			( select b.kunde_nr, min(b.kunde_datum)
			  from  kundeWdatum b
			  where b.kunde_datum > '15-aug-99'
			  group by b.kunde_nr 
            );
begin
	for r in c loop
	    null;
	end loop;

end;
/   Received on Mon Mar 27 2000 - 00:00:00 CST

Original text of this message

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