Database used : Oracle 11g
Operating System : Red hat linux
Technology used : Java & Ibatis
Description :
- Have 2 tables (tableA and table B).
- TableA has more than 100000 records(12- 15 columns).
- TableB has more than 1000000 record(40 -45 columns).
- Need to check column1, column2, column3 and column4 values in tableA with column1 in tableB.
- If its available in tableB then i need to update other columns in tableA.
How to do this?
Issue :
Currently we have done this using java/Ibatis. Frequently we are facing java heap space problems, since its included in, crontab for every 30min it will process the data and makes the environment slower.
Current Approach:
from table A - reference no, user name, age and status where fetched using below query select referenceno, name, age, status from tableA where status = 'FAILED'. its almost returning 20lac rows and the same is stored in arraylist.
from the arraylist, each row is compared with referenceno, name, age in table as below select count(*) from tableB where referenveno= tableA.referenceno and name =tableA.name and age =tableA.age and deleteflag = 'N'.(as mentioned above tableB has almost 40lac records).
If above query returns 1 then, it will update tableA with status as PASSED,
if returns( <1) it will update tableA with status as FAILED and remarks as no matching found.
if returns ( >1) it will update tableA with Status as FAILED and remarks as 'more than one matching is found'.
Hope using shell script / perl can do the job? Please suggest the best way to do this!!!
