Before I start, this is a production system and I am not allowed to alter ANY of the tables involved: so no new columns are allowed, tables can't be changed into being materialized views, and no I can't just re-design the entire application that I've inherited! The ONLY thing I can do is change the SQL code used to perform assorted tasks. The tables involved have all got fresh statistics computed, too, so this isn't a question about improving my DBA techniques. Nor is it a question about tuning the performance of existing code (which already has good execution plans).
I have a large table, called SEARCH. It is a copy of a lot of data from a table called SOURCE (not all of the data, because some is filtered out). SOURCE has about 7 million rows. SEARCH has about 5 million. From time to time, records in SOURCE have their status changed from ACTIVE to INACTIVE. If they are marked INACTIVE, they are not allowed to be in SEARCH. Weekly, we re-synch the SEARCH data with the SOURCE -so, it's during that weekend refresh operation that this sort of query is issued:
delete from SEARCH where IDENTIFIER in (select IDENTIFIER from SOURCE where STATUSCODE != 1);
It's a delete using a standard correlated sub-query. It takes slightly over five minutes to run, which is OK, but I would like to know if there's any alternative SQL or SQL constructs/features/enhancements which could be used to perform the same sort of thing more quickly. As I say, I'm not asking how to tune this sort of thing: I'm asking rather if there are
alternatives to it. If there isn't, that's OK.
I should perhaps add that the STATUSCODE column is not found in the SEARCH table (because if the record's in SEARCH, it would be because the status was =1 in SOURCE originally)