In response to the Jack Silvey (from Richardson, TX ) review, where he wrote "It is the only way to fly.We also have an absolutely incredible stored procedure that rebuilds all of our indexes concurrently after the load, using the Oracle job scheduler as the mechanism of allowing separate threads in pl/sql": Could you provide more information about that procedure and how to rebuild multiple same-table indexes concurrently using Oracle job scheduler? November 19, 2002 - pm UTC instead of begin execute immediate 'alter index idx1 rebuild'; execute immediate 'alter index idx2 rebuild';end;you can code:declare l_job number;begin dbms_job.submit( l_job, 'execute immediate ''alter index idx1 rebuild'';' ); commit; dbms_job.submit( l_job, 'execute immediate ''alter index idx2 rebuild'';' ); commit;end; Now, just set job_queue_processes Thanks, Tom.The only one difference between your code and mine is that I issue just one commit at the end. 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.
It is ournumber 1 reference in times of fear and loathing.
This is what we came up with concerning mass updates INV 50MINVINS 10MINVDEL 7MThere are indexes on INV. KEYExecution Plan ( for deletes and updates )---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 FILTER 2 1 TABLE ACCESS (FULL) OF 'INV' 3 1 INDEX (RANGE SCAN) OF 'IX_INVDEL01' (NON-UNIQUE)alter table INVTMP nologging;-- INVINS contains delta inserts and updatesinsert /* APPEND */into INVTMPselect *from INVINS t1;-- INVDEL contains delta deletes and updatesinsert /* APPEND */into INVTMPselect *from INV t1where not exists ( select null from INVDEL t2 where t2. KEY );alter table INVTMP logging;drop table INV;rename INVTMP to INV-- build indexs etc This is what we came up with and is to the fastestapproach we've tested.
March 17, 2003 - am UTC sorry -- I though it obvious that in most cases "no" is the answer.
we are copying the data or locking excessive amounts of it -- or disabling indexes and the like.
I recorded the time right after the commit statement at the end of PL/SQL block - that's the start time.
Then I kept querying user_jobs view every 2 - 3 sec, until the last of the 5 jobs were gone. 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?
He meant to say they delete the records and some time later the table will be populated again and viceversa.
Tom according to you do you consider partitions for such tables and if yes which type of partition.. November 13, 2002 - pm UTC hard to tell -- is the data deleted by something that is relatively constant (eg: the value in that column doesn't change - so the row doesn't need to move from partition to partition).
The Ask TOM team will be busy at Open World this month. Murali from old_table;index new_tablegrant on new tableadd constraints on new_tableetc on new_tabledrop table old_tablerename new_table to old_table;you can do that using parallel query, with nologging on most operations generating very little redo and no undo at all -- in a fraction of the time it would take to update the data. I don't have a 100million row table to test with for you but -- the amount of work required to update 1,000,000 indexed rows is pretty large.
If you are at the conference, drop into the Groundbreaker area and say Hello. I want to update and commit every time for so many records ( say 10,000 records). Fortunately, you are probably using partitioning so you can do this easily in parallel -- bit by bit.
If so -- sure, cause we could just drop partitions (fast) instead of deleting the data. We also have an absolutely incredible stored procedure that rebuilds all of our indexes concurrently after the load, using the Oracle job scheduler as the mechanism of allowing separate threads in pl/sql.