Shulou(Shulou.com)05/31 Report--

This article mainly introduces "Oracle data loading speed optimization method", in daily operation, I believe many people in Oracle data loading speed optimization method problems have doubts, Xiaobian consulted all kinds of information, sorted out simple and easy to use operation methods, hope to answer "Oracle data loading speed optimization method" doubts helpful! Next, please follow the small series to learn together!

This document describes how to load a large amount of data into a table and insert new data as much as possible;

[Solution]

Set the logging property of the table to NOLOGGING.

When using a subquery to determine which records to insert, include INSERT /*+ APPLD */in the query

Add INSERT /*+ APPLD */to queries that use VALUES clause

Using CREATE TABLE... AS SELECT statement.

[Experimental Test]

1. Create a test table

SQL> create table T01 as select * from all_users where 1=0;

2. View the status of the table

SQL> select table_name, logging from user_tables where table_name = 'T01';

TABLE_NAME LOG

------------- ----------------

T01 YES

3. Modify the table to NOLOGGING

alter table T01 nologging;

4. Create a table TT and insert a large amount of data for comparison purposes;

SQL> create table TT as select * from all_users ;

SQL> INSERT INTO TT SELECT * FROM TT;

SQL> SELECT COUNT(1) FROM TT;

COUNT(1)

----------

1245184

5. Create comparison table T02 without any optimization operation;

SQL> create table T02 as select * from all_users where 1=0;

6. Conduct insertion test of data

Using direct path operation with NOLOGGING method: elapsed time 6.15s

SQL> timing start kk

SQL> insert /*+ APPEND */ into t01 select * from TT;

1245184 rows created.

SQL> timing show;

Elapsed: 00:00:06.15

The conventional method shows that the required time is: 15.39

SQL> timing start kk ;

SQL> insert /*+ APPEND */ into t02 select * from TT;

1245184 rows created.

SQL> timing show;

timing for: kk

Elapsed: 00:00:15.39

Summary: After testing, direct path insertion and nlogging can obviously improve the loading speed of data;

1. Because the least amount of redo can be generated through nlogging;

2. Avoid buffer cache and load data directly into data file, which improves loading efficiency.

But at the same time, this method also has the following problems. After all, you can't take up all the good things. Otherwise oracle would have been set to default configuration long ago;

When inserting data into a table using a direct path, Oracle will automatically insert the core rows above the high water mark, which may cause the table to take up a lot of disk space;

Make sure you submit the data loaded via the direct path, otherwise you won't be able to see it. Because Oracle will report error 0RA-12838, indicating that data loaded by the direct path must be committed before it can be queried.

If T01 data is inserted (backup is performed before insertion), the system needs to be restored due to media failure. At this time, the table RESTORE is restored to the point before failure, and then RECOVER is restored to the point of failure. At this time, the data of table T01 cannot be restored;

[Summary] The above data loading method is not suitable for important business data in the production process, but is more suitable for data extraction or comparison data extraction similar to report class or intermediate library;

At this point, the study of "Oracle data loading speed optimization method" is over, I hope to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.