TBL_CUSTOMER
========
T1_YEAR* SMALLINT(2)
T1_CUSTOMER_NO* CHAR(9)
T1_CUSTOMER_TYPE* CHAR(1)
T1_STATUS_CODE CHAR(1)
T1_LAST_UPDATE_TIMESTAMP TIMESTAMP
TBL_ORDER
=======
T2_YEAR SMALLINT (2)
T2_CUSTOMER_NO CHAR(9)
T2_CUSTOMER_TYPE CHAR(1)
T2_PROCESSED_DATE* DATE
T2_REGION_CODE* CHAR(2)
T2_BATCH_SEQUENCE* SMALLINT(2)
T2_SEQUENCE_NUMBER* INT(4)
========
T1_YEAR* SMALLINT(2)
T1_CUSTOMER_NO* CHAR(9)
T1_CUSTOMER_TYPE* CHAR(1)
T1_STATUS_CODE CHAR(1)
T1_LAST_UPDATE_TIMESTAMP TIMESTAMP
TBL_ORDER
=======
T2_YEAR SMALLINT (2)
T2_CUSTOMER_NO CHAR(9)
T2_CUSTOMER_TYPE CHAR(1)
T2_PROCESSED_DATE* DATE
T2_REGION_CODE* CHAR(2)
T2_BATCH_SEQUENCE* SMALLINT(2)
T2_SEQUENCE_NUMBER* INT(4)
I have a requirement where if a customer has not placed an order within the last five years, a "soft delete" will be carried out (set T1_STATUS_CODE = 'D'), as long as the customer table entry in question hasn't been updated in the last year.
The query I have written times out as there is a huge amount of data in both tables, but especially the order table - unfortunately the customer number is not indexed on the Order table.
Does anybody have any ideas as to how I could make this more efficient? This query will be invoked programatically, and I had thought of using a LIKE predicate on the rightmost 2 digits of the customer number (this should always be numeric despite the CHAR designation) to break the work down into 100 individual tasks.
UPDATE TBL_CUSTOMER
SET T1_STATUS_CODE = 'D'
,T1_LAST_UPDATE_TIMESTAMP = CURRENT_TIMESTAMP
WHERE NOT EXISTS
(SELECT T2_CUSTOMER_NO
,T2_CUSTOMER_TYPE
FROM TBL_ORDER
WHERE T1_CUSTOMER_NO = T2_CUSTOMER_NO
AND T1_CUSTOMER_TYPE = T2_CUSTOMER_TYPE
AND T2_PROCESSED_DATE > DATE(CURRENT_TIMESTAMP - 5 YEARS)
FETCH FIRST 1 ROWS ONLY)
AND RIGHT(T2_CUSTOMER_NO,2) LIKE '%00' (etc...)
AND T1_LAST_UPDATE_TIMESTAMP < CURRENT_TIMESTAMP - 1 YEAR
SET T1_STATUS_CODE = 'D'
,T1_LAST_UPDATE_TIMESTAMP = CURRENT_TIMESTAMP
WHERE NOT EXISTS
(SELECT T2_CUSTOMER_NO
,T2_CUSTOMER_TYPE
FROM TBL_ORDER
WHERE T1_CUSTOMER_NO = T2_CUSTOMER_NO
AND T1_CUSTOMER_TYPE = T2_CUSTOMER_TYPE
AND T2_PROCESSED_DATE > DATE(CURRENT_TIMESTAMP - 5 YEARS)
FETCH FIRST 1 ROWS ONLY)
AND RIGHT(T2_CUSTOMER_NO,2) LIKE '%00' (etc...)
AND T1_LAST_UPDATE_TIMESTAMP < CURRENT_TIMESTAMP - 1 YEAR
Any ideas on how to make this more efficient? Or open to a better solution?