The above script will insert 1 million rows. Insert into test_update(id, name, description) values(i, 'Test Name'||i, 'Test Name '||i||' description ') SQL> create table test_update(id number, name varchar2(100), description varchar2(4000)) storage initial(48M next 4m) To test the above method the following scripts can be used. Then run the PL/SQL script to update the records one by one. To do that create another table which stores the rowids of the records in the original table which has to be updated along with the the value to be updated. The update operation can be made simple by sepearting out the query and update operation. What would be the best way to detect the end of rebuiding, in order to proceed with the next call?
The last question on this topic: is user_jobs view is the right place to look in order to determine that's rebuilding is done and how long it took? In package I am writing, I do massive delete operation, then rebuilding indexes, then starting the next routine. Then I kept querying user_jobs view every 2 - 3 sec, until the last of the 5 jobs were gone. I recorded the time right after the commit statement at the end of PL/SQL block - that's the start time. I said "around", because the technique I used may not be perfect, though it served the purpose. Here is the numbers I've got: rebuilding indexes sequentually consistently took 76 sec., while using dbms_job.submit() calls took around 40 - 42 sec. rows table and rebuild 5 non-partitioned indexes with 'compute statistics parallel nologging' clause.
#Do i need java 8 update 101 if i have update 111 code#
The only one difference between your code and mine is that I issue just one commit at the end. At first, it did not work (job_queue_processes was 0), but after I set it to 12 it started working. Your status said that you had a large backlog, so I decided not to wait for your response and tried myself using dbms_job.submit() calls. Obviously you need to rebuild indecies, etc as required. Alter tablexyz nologging.Ģ) insert /*+ append parallel (xyzhold,12) */ into xyz_hold xyzhold (field1, field2, field3) select /*+ parallel (x,12) */ xyz.field1, my_new_value_for_field2, xyz.field3 from xyz x where blah blah blah.ģ) when done, either rename the table, or swap the partition if your original table is partitioned, and you only updated one partition as we do. When done, we swap the partition of original data with the 'dummy' table (the one containing new values), rebuild indexes in parallel, and wha-la! Our update is complete.ġ) First create your dummy hold table: create table xyz_HOLD as select * from xyz where rownum<1. With nologging, if the system aborts, you simply re-run the 'update' again, as you have the original data in the main table. We institued the Insert into a dummy table append with nologging, and were able to complete the "update" in under 30 minutes. The cursor.For loop approach for the update was calculated to take 53.7 years to complete! One of our apps updates a table of several hundred million records. This is absolutely a viable approach, and one we have used repeatedly.
0 kommentar(er)
