Unanswered: Query regarding BULK UPDATE and INSERT
I am using Oracle 8.1 and have a requirement of dealing with lacs of rows..
The requirement is.. we get lacs of rows as input to us (a datawarehouse project). Currently, we have a procedure which checks the base table
with the key value. If a record exists, then it updates all the other columns (except key columns) and if not exists it inserts that new record.
All this occurs in a cursor and hence it is taking long time.. more than 6-7 hours... I want to tune it.
say.. interface table table1(id,name,age,occupation)
base table is table2(id,name,age,occupation)
my procedure is like this.
cursor cursor1 is
select * from table1 (where condition);
for ir in cursor1 loop
select id,'N' into v_id,v_rec from table2
when no_data_found then
if v_rec='Y' then
insert into table2
I have tried the BULK COLLECT concepts by using Index by tables, but the problem i faced is getting individual values.
I mean, if I use index by tables then I cannot make the UPDATE becuase we cannot reference individual columns from IBT like ir.age, ir.name etc..
There are more than 100 columns in the table and using 100 Index by tables (one for each column) would be difficult.
Could anyone help me by suggesting a good alternative to overcome this problem.
I need some syntax or a pseudocode like above.. so that I can implement it in my project...