Two useful solutions for accelerating mass data processing in Oracle

In fact, it is not difficult to accelerate mass data processing in Oracle. The actual application solution you choose is very important. The following article mainly introduces two solutions to accelerate mass data processing in Oracle, that is, there are also various batch DML operations to improve DML operations.

I. One of the methods to accelerate mass data processing in Oracle and improve DML operations:

Simply put:

1. Pause the index and restore it after update. Avoid re-indexing during the update process.

2. Batch update: submit data every time some records are updated to avoid occupying a large number of rollback segments and temporary tablespace.

3. Create a temporary large tablespace to cope with these update actions.

4. Batch update: submit data every time some records are updated to avoid occupying a large number of rollback segments and temporary tablespace.

5. Create a temporary large tablespace to cope with these update actions.

6. Increase the sorting buffer.

 
 

  
  
alter session set sort_area_size=100000000;  
  
  
insert into tableb select * from tablea;  
  
  
commit;  
 
 

If the index field is updated, the index reconstruction will be involved. The speed of suspending the index will not increase much, but the UPDATE speed may be reduced,

Because the Index Update can improve the data query speed, the speed caused by index reconstruction is not affected.

Oracle optimization and modification of parameters can only increase the performance by 15% at most, most of which are Optimization of SQL statements!

Update is generally slower than insert:

Suggestions:

1. If the updated data volume is close to the whole table, you should not use index instead use full table scan.

2. Reduce Unnecessary indexes because update indexes are usually required for update Tables.

3. If your server has multiple CPUs and uses parellel hint, the efficiency can be greatly improved.

In addition, table creation parameters are very important. For tables with frequent updates, we recommend that you increase the PCTFREE value to ensure that there is sufficient space in the data block for UPDATE, thus reducing CHAINED_ROWS.

Ii. Various batch DML operations for accelerating mass data processing in Oracle:

1) Oracle batch copy:

 
 

  
  
set arraysize 20  
  
  
set copycommit 5000  
  
  
copy from username/password@Oraclename append table_name1  
  
  
using select * from table_name2;  
 
 

2) General insertion methods:

 
 

  
  
insert into t1 select * from t; 
 
 

To increase the speed, you can use the following method to reduce the logs generated during the insertion process:

 
 

  
  
alter table t1 nologging;  
  
  
insert into t1 select * from t;  
  
  
commit;  
 
 

(3) CTAS:

 
 

  
  
create table t1  
  
  
as  
  
  
select * from t;  
 
 

To increase the speed, you can use the following method to reduce the logs generated during the insertion process and set the degree of Parallelism:

 
 

  
  
create table t1 nologging parallel(degree 2) as select * from t;  
 
 

The above content describes the solution for accelerating mass data processing in Oracle, hoping to help you in this aspect.