SQL query problem

I have this sql in my stored procedure, oracle 9i, the value '1120%' in the last line is from a binded varaible:

UPDATE EIM_OPTY A
SET CON_COMMENTS = (SELECT DESC_TEXT FROM S_LST_OF_VAL WHERE TYPE = 'UOB_APPL_ERROR' AND ACTIVE_FLG = 'Y' AND VAL = 'EIM ERROR')
WHERE (IF_ROW_STAT = 'IMPORTED' OR IF_ROW_STAT != 'IMPORTED')
AND IF_ROW_BATCH_NUM > 1
AND ROW_ID IN (SELECT B.ROW_ID FROM EIM_FN_CONTACT1 B, EIM_FN_CONTACT4 C, EIM_OPTY_DTL D, EIM_FN_OPTY1 E, EIM_CONTACT F WHERE B.ROW_ID = A.ROW_ID AND B.IF_ROW_BATCH_NUM >= 1 AND C.ROW_ID = B.ROW_ID AND C.IF_ROW_BATCH_NUM >= 1 AND D.ROW_ID = C.ROW_ID AND D.IF_ROW_BATCH_NUM >= 1 AND E.ROW_ID = D.ROW_ID
AND E.IF_ROW_BATCH_NUM >= 1 AND F.ROW_ID = E.ROW_ID
AND F.IF_ROW_BATCH_NUM >= 1 AND (B.IF_ROW_BATCH_NUM > 1 AND B.IF_ROW_STAT != 'IMPORTED') OR (B.IF_ROW_BATCH_NUM = 1 AND B.IF_ROW_STAT IN ('ID# ALREADY EXISTS', 'CIF# FOUND'))
OR (C.IF_ROW_BATCH_NUM > 1 AND C.IF_ROW_STAT != 'IMPORTED') OR (C.IF_ROW_BATCH_NUM = 1 AND C.IF_ROW_STAT = 'IMPORT FAILED') OR (D.IF_ROW_BATCH_NUM > 1 AND D.IF_ROW_STAT != 'IMPORTED') OR (E.IF_ROW_BATCH_NUM > 1 AND E.IF_ROW_STAT != 'IMPORTED') OR (F.IF_ROW_BATCH_NUM > 1 AND F.IF_ROW_STAT != 'IMPORTED') OR (F.IF_ROW_BATCH_NUM = 1 AND F.IF_ROW_STAT = 'IMPORT FAILED'))) AND ROW_ID LIKE '1120%';

When I have 500 records in each of the tables, the query works fine, but when the record number increases to 5000, the query will hang. Don't think the query is executing too slow and take a long time to finish, because I left it run for a whole night and it still didn't finish executing. Is there anything wrong with the query? I have been running for many many times and I'm still stucked . Please help... Thanks alot in advance!

Best Regards,
string

Are the columns indexed?, Are there any partitions that you are dealing with?, are you putting your driving tables first?, and...are you pulling any CLOB data back?

~jimbag8

are you committing at midpoint by any chance. a strange point: Oracle locks rows for read consistency, but writes blocks. if your update's commit occurs before end of block with updated data, the process can hang as the block is considered complete but still locked!

sorry....

you should run explain plan on the query as well...