SQL & PL/SQL

Oracle version : 10g

Hello,

I have created an UPDATE statement which uses two selects on a single table to do an update according to date criteria.

Due to the fact that the update has different criteria for updating according to whether the date falls within one of two ranges
(future or past) two selects were used together with a union between them to unite the results.

The end result is of course two full table scans and a poorly performing execution.

The goal is to update a field XDEL (type number) based on a date column value (column XDATE) where the dates of interest
fall both in the past (less than sysdate minus 45 days) and in the future (greater than sysdate +90 days). For records with XDATE
values within these ranges, I wish to set field XDEL=1

For records in the future (>=sysdate+90):
for a unique combination of XNAME+XCODE, where XDATE is within the date range for update, set XDEL=1
but for a non-unique combination of XNAME+XCODE+XDATE, update XDEL=1 only for the 'oldest' future record, ie update XDEL for MIN(XDATE) only

For records in the future (< sysdate+45):
for a unique combination of XNAME+XCODE, where XDATE is within the date range for update, set XDEL=1
for a non-unique combination of XNAME+XCODE, update XDEL=1 only for the 'newest' past record, ie update XDEL for MAX(XDATE) only

The combination of XNAME+XCODE XDATE is unique. As an example of the 'in the past' scenario:

XNAME | XCODE | XDATE
================
1. AAAA ~ 002 ~ 01/01/2006
2. AAAA ~ 002 ~ 02/01/2006
3. AAAA ~ 002 ~ 03/01/2006
4. XXXX ~ 123 ~ 02/01/2006

Here, we would update XDEL in record 3. as it corresponds to MAX(XDATE) for the GROUP AAA002 (and we would also update record 4. as a unique record that meets the criteria)

As an example of the 'in the future' scenario:
XNAME | XCODE | XDATE
================
1. HHHH ~ 002 ~ 01/01/2011
2. HHHH ~ 002 ~ 02/01/2011
3. HHHH ~ 002 ~ 03/01/2011
4. XXXX ~ 123 ~ 02/01/2011

Here, we would update XDEL in record 1. as it corresponds to MIN(XDATE) for the GROUP HHHH002 (and of course 4. as a unique record that meets the criteria)

Here is a query that works, but is slow:

UPDATE TAB1 SET XDEL=1 WHERE (XNAME,XCODE,XDATE) IN ((SELECT XNAME,XCODE,max(XDATE) FROM TAB1 WHERE XDATE < sysdate-45 GROUP BY XNAME,XCODE) UNION (SELECT XNAME,XCODE,min(XDATE) from TAB1 WHERE XDATE >= sysdate+90 GROUP BY XNAME,XADDR));

XDATE is a DATE type
XCODE and XADDR are VARCHAR2
XDEL is number type

Any ideas would be greatly appreciated.