Re: suggestion to improve a query

One point to consider is that if the
date is the smallest date greater
than 15th August, then it must be
greater than 15th August.

Add the condition:

    WHERE A.kunde_datum > '15-Aug-1999'

and you will reduce the number of rows that invoke the correlated sub-query without loss of data.

(This predicate can be applied to any of the suggestions you have received to date)

Another strategy, which may need hinting is:

    select all customers where date > 15th Aug     and NOT EXISTS select matching customer     with date < selected row

The suggestion that you use a driving IN query might go faster if you hinted it into using the PK index into table instead of doing the hash join.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Tisan Gabriel wrote in message <8b518i$j82$1_at_pollux.ip-plus.net>...


>

>SELECT /*+ first_rows */ * FROM kundeWdatum A

>WHERE A.kunde_datum =

>(SELECT  MIN(B.kunde_datum) AS DATE_MINIMUM FROM kundeWdatum B

>WHERE B.kunde_datum > '15-Aug-1999' AND B.kunde_nr = A.kunde_nr GROUP BY

>B.kunde_nr)

>