All, I have a requirement to do a bulk extract,nightly extract from 5 of our tables and insert the data using a dblink to a rempte database. All,
I have a requirement like this
1. I will have upto 200 Ids[unique] in Table A, the extract should read the ids from Table A and then extract data from 5 other tables where id=ids from table A. After extracting data the data needs to be loaded in remote tables[same tables as of table b,c,d and e] using a dblink and those ids which are succesfully extracted should be deleted from Table A. I was thinking about creating a cursor and then iterate through the cursor and then extract data from other tables.
Curosr- select id from Table A
iterate thru the cursor and then
select * from table b where id=..
select * from table c where id =
select * from table d where id=
select * from table e where id=
2. Question is Is it a good appraoch ? For each id there can be tons of records in other tables
Example: Table b can have 30000 records for id1
Table C can have 35000 recods for id1
Table D can have 20000 records for id1
Table E can have 30000 records for id1
Similiarly there can be upto 200 Ids and then huge extracts for all the ids from different tables, This will be a batch extract and will be done during off hours.
How can this be handled efficiently ?
Can a cursor handle it ?
Can anyone suggest me some efficient ways ?
rather than using a cursor I tried a simple insert also, like this
INSERT INTO
x_test(x1,x2,x3,x4,x5,x6,x
7,x8)
SELECT
x1,x2,x3,x4,x5,x6,x7,x8 from
x
where id in (select id from log_x); [For one table only I tried it, not the 5 tables], The problem is if an id fails to get processed how will I keep track of that ? Because after a succesful extract I have to delete those ids which are extracted from log table also.
Thanks
Start Free Trial
