Shulou(Shulou.com)06/01 Report--

This article introduces you / * + APPEND*/ insertion performance example analysis, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Using the append prompt for insert is called direct path loading insertion.

[characteristics]

1. Use this prompt because the system does not look for free blocks in the freelist linked list, but inserts data directly above the high water mark, so it is faster. But for tables that are often delete, the waste of disk space is too large!

2. It does not log, so once the inserted data is not saved back to disk, the inserted data cannot be redone.

3. After / * + append*/, you need to submit it immediately, otherwise the next modification failure (insert,update,delete) will be affected.

In Oracle Database 10g Enterprise Edition Release 10.2.0.1.0, if the syntax for the insert operation is

Insert / * + append*/ into XXX values (XXX)

Then the system adds a row exclusive lock, which is the row-level lock added by a normal insert, but if the syntax of the insert operation is

Insert / * + append*/ into XXX select XXX from XXX

Then the system adds an exclusive lock, which is equivalent to a table-level lock, which means that no other session can perform insert,update,delete operations when there is no commit in this session.

Check the database archive mode (NOARCHIVELOG, ARCHIVELOG):

Archive log list

Or

SELECT log_mode from v$database

View the current redo log size:

Select name,value,class from v$sysstat where name='redo size'

Or turn on Statistics, which can more accurately count the redo generated by the current statement. It is recommended to use the

Set autotrace traceonly statistics

Normal insertion method:

Insert into t select * from dba_objects

Append insertion method:

Insert / * + append*/ into t select * from dba_objects

Append+nologing insertion method:

Insert / * + append*/ into t nologging select * from dba_objects

[test conclusion]

1. Whether in archive mode or non-archive mode, append+nologing insertion works best and generates the smallest redo logs.

2. Append insertion mode works well in non-archive mode.

3. In archive mode, append insertion mode has no effect.

[my test]

As can be seen from the following comparison, with the increase of hint method, the speed is increased by 4 times.

It's really amazing, it's all 1.8 million-row copies of the table, and my test is as follows:

Insert into p_nbsc_ho1 select * from p_nbsc_ho (73 minutes consumed)

Insert / * + append*/ into p_nbsc_ho2 select * from p_nbsc_ho (takes 7 minutes)

Disadvantages:

Insert / * + append*/ adds a level 6 lock to the table if it is not commit, that is, the select table will report an error even at this time.

Therefore, the statement prompted by append cannot be a business table at first, and then it should be submitted to commit as soon as possible, so it is generally afraid to use hint.

This is the end of the example analysis of / * + APPEND*/ insertion performance. I hope the above content can be helpful to you and learn more. If you think the article is good, you can share it for more people to see.

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.