i am using EXISTS clause here. that makes the query slow down for 100 million records. i am using EXISTS clause in the sub query because, i want only those rows should get updated. can any help me to come out of this problem
Second subquery should be exactly the same as the first and it isn't
Also you didn't post
Four digit database version
results from dbms_xplan.display
table and index definitions.
In an EXISTS subquery you should preferably use a dummy column like 'X' or 1, to force Oracle to read only the index and skip reading the table.
Senior Oracle DBA