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. 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 have another table B containg 10,000 records of incremented and edited records of A table. I am using the following codes to append data from B to A.--For incremental/New data-----insert into Aselect * from B where column_name NOT IN(select column_name from B);--For Edited Data-------cursore C_ABselect * from Bminusselect * from AFor R in C_ABloop Update A set....where ..loop End; It's working but taking a huge time/sometimes hang the computer. That means -- just using math here -- that we have 600 seconds, 12,000 queries to run, 12000/60 = 20, so we are doing 20 per second -- or each query is taking 0.05 cpu seconds to run.0.05 cpu seconds is awesome for a anything 12,000 times and you might have a problem tho! this might be one of the rare times that a temp table can be useful.
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. I thought that /* NOLOGGING */ skip redo/undo (to simplify). only bulk operations can use it.insert /* append */ can skip logging of the TABLE data since append writes above the high water mark (does not touch ANY existing data).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! DATE_SERVICE May 13, 2004 - pm UTC no, I mean:create global temporary table gtt( b_elig_key ..., client_member_id ... DATE_SERVICE, 'YYYYMMDD')) May 15, 2004 - pm UTC how many rows to be updatedis eligibility key indexed (are you mass updating an indexed key)is your update bumping into other row level updates.updating millions of rows is a couple minute process ifo column is not indexedo you are not contending for the dataupdating millions of rows is a couple (hour|day|week|month|year) process potentially otherwise.(one thing I forgot to mention I think -- use dbms_stats.set_table_stats to set the numrows in the gtt using sql%rowcount after the insert so the optimizer has a clue) Tom, In the updated table (STG_CLAIM_TRY) all the records will be updated (value or null), around 5,000,000.
Note - I didn't design this system, but I have to work with it. Ok, this is the reall query: UPDATE STG_CLAIM_TRY A SETA. 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.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. ELIGIBILITY_KEY B_ELIG_KEY FROM STG_F_ELIGIBILITY_TRY B, TMP_STG_CLAIM_TRY a WHERE A. PRIMARY KEY )on commit delete rows;once in your database, then to update:insert into that gtt the job of A and B as above (add client_id to the select list) and the update the join of the gtt to the A table. DATE_SERVICEFROM STG_CLAIM_TRY A, STG_F_ELIGIBILITY_TRY B WHERE A. There are no indexes or constraints on STG_CLAIM_TRY.
But the 2nd Where clause simply return the message of `more than one row is return', since the id is unpredictable and this create a `many to many' relationship in both tables. Many Thanks,(script)REM* the where-clause of the update cannot work UPDATE table b SET column_b1 = ( SELECT MAX(column_a1) FROM table_a a, table_b b WHERE a.id=GROUP BY a.id)WHERE table_IN (SELECT MIN(id)FROM table_a GROUP BY id); Your example is somewhat confusing -- you ask "update column a1 in table a where data in column b1 in table b" but your update shows you updating column b1 in table B with some data from table a. I this updation I want to find the equalent date/day in previous year corresponding to current year date and update the amount.
Additionally -- given the way the where and set clauses are CODED in the above -- it would succeed. For this I am using another table to find previous year dates for current year dates.The pl/sql block first tries to insert if exception occur because of sqlcode = -1 then it knows it is for update. Either: o move the error log into an autonomous transaction o just LOSE the commit all together (without changing the outcome of your program at all!! o you hit an error on the update o you log it and commit o you subsequently (later) hit an ora-1555 on the SELECT and bump out of the loop whoops -- you processed 1/2 of the table. Since I am inserting a massive amount of data, using Merge -1.) How can I commit at every 1000 rows2.) If the row to load fails during Insert or update, can i trap that rows and put it into another table T3 .3.) How can i find out how many rows are inserted verser update.4.) Is Merge the best case in above scenario. I'll assume you *meant* b.mid_table_idas long as that column is not indexed, this won't take very long at all.update ( select a.mid_table_id a_mtid, b.mid_table_id b_mtid from big_table a, mid_table b where a.join_col = b.join_col ) set a_mtid = b_mtid;that assumes (naturally) that a primary/unique constraint exists on mid_table(join_col) -- if not, add one -- it *must be true* or the update would be ambigous (if many rows can exist in B, which one to use? Tom we have a table with the following structure :inv_id addr_1 addr_2 addr_3 addr_4 1 xxx null null yyy 2 null xxx null yyy 3 null null xxx yyy 4 null null null xxx 5 xxx null yyy null 6 xxx null yyy zzz output should be :inv_id addr_1 addr_2 addr_3 addr_4 1 xxx yyy null null 2 xxx yyy null null 3 xxx yyy null null 4 xxx null null null 5 xxx yyy null null 6 xxx yyy zzz null all Null values should move into one side, and all Not Null values should move into one side. Thanksdmv Also if your followup includes an example you want me to look at, I'll need it to have a create table, insert into statements and such that I can easily cut and paste into sqlplus myself (like I give you) in order to play with. update 2 ( 3 select id, olda, oldb, oldc, oldd, 4 a, 5 b, 6 decode(shift,0,c,d) c, 7 decode(shift,0,d,null) d 8 from ( 9 select t.*, decode(c,null,1,0) shift 10 from ( 11 select id, olda, oldb, oldc, oldd, 12 a, 13 decode(shift,0,b,1,c,2,d) b, 14 decode(shift,0,c,1,d) c, 15 decode(shift,0,d,null) d 16 from ( 17 select t.*, decode(b,null,decode(c,null,2,1),0) shift 18 from ( 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 ) 28 ) t 29 ) 30 ) t 31 ) 32 ) 33 set olda = a, oldb = b, oldc = c, oldd = d 34 / 6 rows updated.