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

This article will explain in detail how to reduce the redo_size of batch insert by NoLogging Append in Oracle. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

In business processing, the real table temporary table is often used to process the intermediate results, and the Insert operation of the real table records redo log by default. The relevant test summary information is collected to solve this problem:

Comparison of redo and undo sizes generated by common dml and ddl statements using the nologging option

DDL/DML Operations

Direct-path

Nologging

Parallel

Noarchivelog Redo

Noarchivelog Undo

Archivelog Redo

Archivelog Undo

Insert into XXX select * from YYY

N

N

N

19076324

627240

18938620

612980

Alter table XXX nologging

Insert into XXX select * from YYY

N

Y

N

19085860

631584

18935548

612912

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

Y

N

N

26628

4048 (only metadata)

19145656

4048

Alter table XXX nologging

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

Y

Y

N

26868

4048 (only metadata)

26836

4048

Create table XXX as select * from YYY

Y

N

N

77624

18500

19162220

15468

Create table XXX nologging as select * from YYY

Y

Y

N

52160

11176

52408

11248

Alter table XXX move nologging

N

Y

N

36288

6208

36576

6208

Alter table XXX move

N

N

N

39788

7288

19154024

5972

Create index XXX

N

N

N

21280

1864

3093616

1888

Create index XXX nologging

N

Y

N

22264

2208

22620

2232

Update XXX set

N

N

N

122903212

47987880

122403692

47786680

Update / * + parallel (5) * / XXX set

N

N

Y (Queries)

121629928

46706296

120818336

46466056

Alter table XXX nologging

Update XXX set

N

Y

N

123155288

48006404

110396512

41617700

Alter table XXX nologging

Update / * + parallel (5) * / XXX set

N

Y

Y (Queries)

119748064

46152324

120205036

46261536

Alter session enable parallel dml

Update / * + parallel (5) * / XXX set

N

N

Y (DML)

59846488

24680220

59740516

24611672

Alter table XXX nologging

Alter session enable parallel dml

Update / * + parallel (5) * / XXX set

N

Y

Y (DML)

59831756

24673396

59717116

24602628

Delete XXX

N

N

N

60684720

37650208

61770472

38254648

Delete / * + parallel (5) * / XXX

N

N

Y (Queries)

60685416

37650436

61988568

38461832

Alter table XXX nologging

Delete XXX

N

Y

N

60684956

37650216

61989984

38460424

Alter table XXX nologging

Delete / * + parallel (5) * / XXX

N

Y

Y (Queries)

60685092

37650224

61986156

38459172

Alter session enable parallel dml

Delete / * + parallel (5) * / aabbn

N

N

Y (DML)

65072896

40990412

61979216

38457372

Alter table XXX nologging

Alter session enable parallel dml

Delete / * + parallel (5) * / aabbn

N

Y

Y (DML)

65073828

40998048

62022668

38500548

Conclusion:

1. Insert into: both noarchivelog and archivelog modes generate a large amount of undo in non-direct mode, a small amount of undo in noarchivelog mode in redo;direct mode, a small amount of undo in archivelog mode in redo;direct+logging mode, a small amount of undo and redo in archivelog mode in large amount of redo;direct+nologging mode

2. Create table as:CTAS itself is a kind of direct operation. Noarchivelog mode produces a small amount of redo in CTAS+logging mode, noarchivelog mode produces less redo in undo;CTAS+nologging mode, archivelog mode produces a small amount of undo in undo;CTAS+logging mode, archivelog mode produces a small amount of undo and redo in a large number of redo;CTAS+nologging mode.

3 、 alter table... Noarchivelog mode produces a small amount of undo in move:nologging mode, archivelog mode produces a small amount of undo in redo;nologging mode, and archivelog mode produces a small amount of undo and a lot of redo in a small amount of redo;logging mode

4. Noarchivelog mode produces a small amount of undo in create index:logging+nologging mode, archivelog mode produces a small amount of undo in redo;logging mode, and archivelog mode produces a small amount of undo and redo in a large number of redo;nologging mode.

5. In update:enable parallel mode, noarchivelog+ archivelog mode generates a small amount of undo and a small amount of redo; in non-enable parallel mode, while noarchivelog+ archivelog mode generates a large number of undo and a large number of redo

6. Delete: any combination will generate a lot of undo and a lot of redo

Other reference lists:

1) when will Nologging take effect? Http://www.eygle.com/faq/Nologging&append.htm

2) the method of reducing redo size by Oracle

In non-archived mode: append can greatly reduce the amount of redo.

In archive mode: in tablespace and database-level non-force logging mode, append can greatly reduce the amount of redo if the table is nologging.

This is the end of this article on "how to reduce the redo_size of batch insert in NoLogging Append in Oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out 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.