T2_YEAR SMALLINT (2)
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.
SET T1_STATUS_CODE = 'D'
,T1_LAST_UPDATE_TIMESTAMP = CURRENT_TIMESTAMP
WHERE NOT EXISTS
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?