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 am using Table T1 to insert or update the data inthe Table T2 (which has a unique key column -c1 c2) in oracle 8.1.7. COM merge into t1 2 using t2 3 on ( t2.object_id = t1.object_id ) 4 when matched then 5 update set t1.object_name = t2.object_name 6 when not matched then 7 insert (object_id, object_name) values( t2.object_id, t2.object_name); 29317 rows merged. In my case Table T1 (staging table with 300 columns) will have 200 thousand rows every day for insert or update to the history table T2 ( 280 columns) that has 10 Million historical rows. For this I am using another table to find previous year dates for current year dates.2.) All rows failed during INSERT/u Pdate log them to a Fault table 3.) Find out number of rows inserted verses update. Ok, this is the reall query: UPDATE STG_CLAIM_TRY A SET A. ELIGIBILITY_KEY FROM STG_F_ELIGIBILITY_TRY B WHERE A. ) A failure in the middle of an append into a table -- harmless, the temporary extents we were writing to just get cleaned up.I am using the PL/SQL to handle this, the Inserts are fine but the Updates are very slow. The test PL/SQL is as follows - declare todate date; current_mode char(10) :='load'; exec_code varchar2(200); exec_message varchar2(2000); this_total number :=0; this_succ number:=0; this_update number:=0; this_fail number:=0; this_insert number:=0; this_start char(10) ; this_end char(10); this_table_name varchar2(10) :='test'; cursor c is select * from test_temp ; commit_point number :=0; test_case number := null; fault_id varchar2(30); begin select to_char(sysdate, 'hh24:mm:ss') into this_start from dual; delete from test_load_result where table_name =upper(trim(this_table_name)); commit; for xc1 in c loop this_total:=this_total 1; commit_point:=commit_point 1; if commit_point 10000 then commit; commit_point:=0; end if; declare begin test_case := xc1.test_jdate; insert /* append */ into test( test_id, test_code ,test_case ,test_month ,test_jdate , sequence_no , check_status ) values ( trim(xc1.test_id), trim(xc1.test_code), to_date( to_char(to_date('','mm-dd-yyyy') ,'j') abs(test_case),'j'), upper(to_char((to_date( to_char(to_date('','mm-dd-yyyy') ,'j') abs(test_case),'j')),'mon')), trim(xc1.test_jdate), trim(xc1.sequence_no), trim(xc1.check_status) ); this_succ:=this_succ 1; exception when others then if sqlcode = -1 then declare begin update test set check_status = trim(xc1.check_status) where test_id = trim(xc1.test_id) and test_code=trim(xc1.test_code) and test_jdate = trim(xc1.test_jdate) and sequence_no =trim(xc1.sequence_no) ; this_update := this_update 1; exception when others then exec_code := sqlcode; exec_message := sqlerrm; select sysdate into todate from dual; if (this_update 0) then this_update := this_update - 1; end if; this_fail:=this_fail 1; fault_id:= trim(xc1.rms_index); insert into test_fault select (select sysdate from dual), st.* from test_temp st where trim(rms_index)=trim(fault_id); insert into test_exception( exec_date, exec_mode, table_name,rms_index, exec_code, exec_message) values (todate,current_mode,this_table_name,fault_id,exec_code,exec_message); end; else exec_code := sqlcode; exec_message := sqlerrm; select sysdate into todate from dual; this_fail:=this_fail 1; fault_id:= trim(xc1.rms_index); insert into test_fault select (select sysdate from dual), st.* from test_temp st where trim(rms_index)=trim(fault_id); insert into test_exception( exec_date, exec_mode, table_name,rms_index, exec_code, exec_message) values (todate,current_mode,this_table_name,fault_id,exec_code,exec_message); end if; end; end loop; this_succ := this_succ this_update; select decode(sign(this_succ -(this_update this_fail)),1,this_succ -(this_update this_fail),0) into this_insert from dual; select to_char(sysdate, 'hh24:mi:ss') into this_end from dual; test_results(todate,this_start, this_end, this_table_name, this_total, this_succ,this_insert, this_update, this_fail); commit; end; / November 08, 2002 - pm UTC you do realize that /* append */ hint is just a waste of keystrokes right? nologging on an index only affects things like: o create (no existing data) o rebuild (no existing data is touched) see Tom, Thanks for the Clarification of NOLOGGING.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.I have a table named A containing say 100000 records. Analyze, use the CBO and look for nice big juicy HASH JOINS Hi Tom Thankyou very much for your query. DATE_SERVICE May 12, 2004 - pm UTC my concept now, that the join is "fast" is to use a global temporary table with a primary key - insert the results of the select join into it and update the join (which we can do since the gtt will have a proper primary key on it) Tom, I tried that but I think I'm doing something wrong. There are no indexes or constraints on STG_CLAIM_TRY.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.l_icnt ); 17 dbms_output.put_line( 'Updated '