I'm having a little trouble designing a code that will run fast.
My requirments are:
I have a table SCD(180mil records) and a smaller table LOG(about 300 records).
REAL_KEY | FIC_KEY
SCD structure :
Another_KEY | SERIAL_KEY ....
I need to delete from SCD all the real key's, where also exists a record with FIC_KEY, so something like
delete from scd t
where serial_number in(select real_key from log l1)
and exists(select 1 from scd s,log l2 where s.serial_key = l2.fic_key
and l2.real_key = l1.real_key)
The problem is I cant use the first correlated query results to compare the second (l2.real_key = l1.real_key). Also, even if it will run, this probably will take a lot of time since SCD contains a lot of records.
Any help would be appriciated.