>Martijn Tonies
>select c.name, c.surname, c.telephone, c.lastorderdate, o.customerlink
> from customers c left join orders o on (o.customerlink = c.link
>and o.recdate > '01-jan-2004' and o.recdate < '01-feb-2004' and
>o.branchcode= 'SAN')
>where c.branchcode = 'SAN' and o.customerlink is null
> order by c.lastorderdate desc,1,2

PLAN SORT (JOIN (C INDEX (IDX_CUSTOMERS_BRANCHCODE),O INDEX
(ORDERS_CUSTOMERLINK,ORDERS_RECDATE)))
That query executes in 46 seconds

> Daniel Rail
> select c.name, c.surname, c.telephone, c.lastorderdate
> from customers c
> where not exists (select 1 from orders
> where customerlink = c.link
> and recdate > '01-jan-2004'
> and recdate < '01-feb-2004'
> and branchcode = 'SAN')
> and c.branchcode = 'SAN'
> order by c.lastorderdate desc,1,2

PLAN SORT ((C INDEX (IDX_CUSTOMERS_BRANCHCODE)))
PLAN (ORDERS INDEX (ORDERS_CUSTOMERLINK,ORDERS_RECDATE))
This also takes 45 seconds?

>Luc
>select
>c.name, c.surname, c.telephone, c.lastorderdate
>from customers c
>where NOT EXISTS (
> select customerlink from orders
> where
> recdate > '01-jan-2004' and
> recdate < '01-feb-2004' and
> branchcode = 'SAN'
>)
>and c.branchcode = 'SAN'
>order by
>c.lastorderdate desc,1,2

Luc, this is fast (0.3s) but it doesn't return any data? How does the NOT
EXISTS clause work?

Martijn and Daniel, I think there may be an issue with PLAN chosen. I am
going to try some manual plans and see what happens if I cant figure out
Lucs idea

Thanks for the input
Gary