Updating a column in oracle dating professionals in new york city
(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!
Note - I didn't design this system, but I have to work with it. Elapsed: .22 that won't "hang the computer" for very long.. 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. Thanks dmv 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.sql%rowcount ); 13 end; 14 / Updated 29317 Inserted 0 PL/SQL procedure successfully completed. 3.) How can i find out how many rows are inserted verser update. Thanks November 06, 2002 - pm UTC 1) you don't want to -- incrementation commits -- baaaaaddddd terrible practice. (answer = you cannot, the statement is transactional. one would need an example (complete, yet concise, with sample data and an explanation of how the data all fits together) hi tom , desc child_table ------------------- child_id number ; child_birth_date date; child_20_flag number(1) ; where the child_20_flag should indicate if the child reach 20 years old or not.
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.