Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: suggestion to improve a query
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;