ANBOB™

Today I read foreign blog,I think I learned the true knowledge

One of most operations we are performing is “Deleting many rows from oracle big tables” ;
Ths most common / easier query we always have in mind is :

BEGIN
DELETE FROM MY_BIG_TABLE WHERE MY_COLUMN
COMMIT;
END;
/
The problem the query can take hours to finish and may generate a huge amounts of archives logs;

3 solutions can be used here:

Solution1:
create table MY_NEW_BIG_TABLE NOLOGGING as select * from MY_BIG_TABLE where … ;
drop table MY_BIG_TABLE ;
rename MY_NEW_BIG_TABLE to MY_BIG_TABLE ;
create index old_table_idx1 on MY_BIG_TABLE (My_col1,My_col2) NOLOGGING parallel 2 tablespace INDX;
…..

1- A new table is created Without log generated during the operation.
2- Move the data to a new table, drop and then rename the old table by a new one.
3- Create indexes as fast as possible , without log during the creation of the index.

Solution 2:

Partition the data, do a parallel delete. Each partition will use its own rollback segment, each will run in parallel.

Solution 3:

Partition the data so that you can do a Truncate the partition instead of DELETE.

打赏

微信扫一扫，打赏作者吧～