The staging instance tables looks exactly like the main tables other than a timestamp and an update_type column are added. I need to update 1 column (number) with a column from another table (around 350,000 records) based on a join value in the 2 tables. Can you think of a good way to do this in as little time as possible? you do realize your update sets a.mid_table_id to either: a) itself (you selected a.mid_table_id again) b) null if nothing exists in b probably *not* what you meant?
The records must be processed in order so that for instance, if a record is updated, deleted, inserted, then updated again (not likely, but it *could* happen) those operations should happen in the correct order. In the first table (the 5,000,000 record table) all the records will be updated. (I'll be happy with anything under 20 minutes) Thanks in advance. I'll assume you *meant* b.mid_table_id as long as that column is not indexed, this won't take very long at all.
Oracle will return ORA-01031 (insufficient privileges). Hi Tom, I have a related question for an UPDATE - that takes unexpected long time. You said to use Oracle9I Merge statement when inserting or updating the data from a stagging table to history table. I this updation I want to find the equalent date/day in previous year corresponding to current year date and update the amount.
I have another table B containg 10000 records of incremented and edited records of A table. I am using the following codes to append data from B to A. consider: [email protected] merge into big_table B 2 using small_table S 3 on ( = ) 4 when matched then update set 5 b.owner = s.owner, b.object_name = s.object_name, 6 b.subobject_name = s.subobject_name, b.object_id = s.object_id, 7 b.data_object_id = s.data_object_id, b.object_type = s.object_type, 8 b.created = s.created, b.last_ddl_time = s.last_ddl_time, 9 b.timestamp = s.timestamp, b.status = s.status, b.temporary = s.temporary, 10 b.generated = s.generated, b.secondary = s.secondary 11 when not matched then insert 12 ( ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, 13 OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, 14 GENERATED, SECONDARY ) 15 values 16 ( s. Surely next time, i will follow your instructions regarding create and insert statements, which helps you to answer quickly. Regards dmv Tom, I am using the cost based optimizer, I followed your suggestion and analyzed the 2 tables, this is what I get now. ELIGIBILITY_KEY FROM STG_F_ELIGIBILITY_TRY B, STG_CLAIM_TRY a WHERE A. The updating session is the only session in the db. create table testupdate (sno number(4), itemcode varchar2(8), value number(4), appliedvalue number(4)) insert into testupdate (sno,itemcode,value) values(1,'item1',200); insert into testupdate (sno,itemcode,value) values(2,'item2',100); insert into testupdate (sno,itemcode,value) values(3,'item3',300); insert into testupdate (sno,itemcode,value) values(4,'item4',200); insert into testupdate (sno,itemcode,value) values(5,'item5',50); insert into testupdate (sno,itemcode,value) values(6,'item6',200); insert into testupdate (sno,itemcode,value) values(7,'item7',400); SNO ITEMCODE VALUE APPLIEDVALUE -------- -------- ---------- ------------ 1 item1 200 2 item2 100 3 item3 300 4 item4 200 5 item5 50 6 item6 200 7 item7 400 Now: I'm writing a stored procedure in which I have to update the appliedvalue column of the above table.--For incremental/New data----- insert into A select * from B where column_name NOT IN (select column_name from B); --For Edited Data------- cursore C_AB select * from A minus select * from A For R in C_AB loop Update A set....where ... ****************************************************** to finish. Im with you on the fact that this update should take no more then few minutes but its not :-) . DATE_SERVICE, 'YYYYMMDD')) May 24, 2004 - pm UTC search this site for ora-01555 also ask yourself, so, what happens when we crash in the middle of the loop. For this I have something called the actual Value which is stored in the variable vnum_actual Value.end loop End; It's working but taking a huge time/sometimes hang the computer. to update 10,000 rows in a 100,000 row table should take seconds (it'll be a direct function of the number of indexes). Is there any other information that I can provide you with to help shade some light on this pain in the neck update? I changed the global temporary table to index organized table, the insert takes minutes and the update never finish (its still running now for about 30 minutes already). DATE_SERVICE FROM STG_CLAIM_TRY A, STG_F_ELIGIBILITY_TRY B WHERE A. can I restart that process or did the programmer not even begin to think about that eventuality? June 15, 2004 - pm UTC 1) b must have a primary key, yes. where did i show merge being faster than a single update? This value in the variable vnum_actual Value should be distributed among the appliedvalue column as follows.(Nt- As i am loading data into temporaray table from a legacy system, i want this load to be as fast as possible so i am not putting any constrain on temp table and handling most of data error inside oracle. If you don't see what the query is doing -- please run it bit by bit from the inside out. first, how come /* NOLOGGING */ will do nothing for updates? DATE_SERVICE May 11, 2004 - am UTC nologging is *not a hint* nologging is an attribute of a segment. insert /* append */ can skip logging of the TABLE data since append writes above the high water mark (does not touch ANY existing data).All fields in the temporary tables are varchar2) The actual Temp as well as Target table has 300 plus columns. starting with: 25 select a olda, b oldb, c oldc, d oldd, t.*, decode(a,null,decode(b,null,decode(c,null,3,2),1),0) shift 26 from t and then 19 select id, olda, oldb, oldc, oldd, 20 decode(shift,0,a,1,b,2,c,3,d) a, 21 decode(shift,0,b,1,c,2,d) b, 22 decode(shift,0,c,1,d) c, 23 decode(shift,0,d,1,null) d 24 from ( 25 select a olda, b oldb, c oldc, d oldd, t.*, decode(a,null,decode(b,null,decode(c,null,3,2),1),0) shift 26 from t 27 ) and so on -- you'll see how I built it. I thought that /* NOLOGGING */ skip redo/undo (to simplify). insert /* append */ cannot skip logging of the INDEX data on that table, regardless of the nologging attribute of an index -- since you are mucking about with EXISTING DATA (and a failure in the middle would destroy your DATA!