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

This article analyzes "how to test CTAS and insert append". The content is detailed and easy to understand. Friends who are interested in "how to test CTAS and insert append" can follow the editor's idea to read it slowly and deeply. I hope it will be helpful to you after reading. Let's learn more about "how to test CTAS and insert append" with the editor.

A test on 8174, non-archived mode:

Code:

SQL > select * from v$version

BANNER-Oracle8i Enterprise Edition Release 8.1.7.4.1-Production

PL/SQL Release 8.1.7.4.0-Production

CORE 8.1.7.2.1 Production

TNS for 32-bit Windows: Version 8.1.7.4.0-Production

NLSRTL Version 3.4.1.0.0-Production

SQL > archive log list database log mode non-archiving mode

Automatic archiving enabled

End of Archive D:databaseoracleora817RDBMS

The earliest profile log sequence 1488

Current log sequence 1491

SQL > select * from redo_size

VALUE-

91848

SQL > create table test nologging as select * from all_objects

The table has been created.

SQL > select * from redo_size

VALUE-

147148

SQL > drop table test

The watch has been discarded.

SQL > select * from redo_size

VALUE-

177584

SQL > create table test as select * from all_objects

The table has been created.

SQL > select * from redo_size

VALUE-

232892

SQL > select (232892-177584) redo, (147148-91848) redo_nolog from dual

REDO REDO_NOLOG--

55308 55300'

What happens in archive mode: code:

The SQL > shutdown immediate; database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 65648668 bytes

Fixed Size 75804 bytes

Variable Size 44523520 bytes

Database Buffers 20971520 bytes

Redo Buffers 77824 bytes

The database is loaded.

SQL > alter database archivelog

2 /

The database has changed.

SQL > alter database open

The database has changed.

SQL > drop table test

The watch has been discarded.

SQL > select * from redo_size

VALUE-

30520

SQL > create table test as select * from all_objects

The table has been created.

SQL > select * from redo_size

VALUE-

2953668

SQL > drop table test

The watch has been discarded.

SQL > select * from redo_size

VALUE-

3070020

SQL > create table test nologging as select * from all_objects

The table has been created.

SQL > select * from redo_size

VALUE-

3125328

SQL > select (2953668-30520) redo, (3125328-3070020) redo_nolog from dual

REDO REDO_NOLOG--

2923148 55308'

For a further test in archive mode, compare the redo size: code of ctas and ctas myriad data + insert append:

SQL > drop table test

The watch has been discarded.

SQL > select * from redo_size

VALUE-

3155764

SQL > create table test as select * from all_objects where 1: 0

The table has been created.

SQL > insert / * + append*/into test select * from all_objects

25474 rows were created.

SQL > commit

The submission is complete.

SQL > select * from redo_size

VALUE-

6079860

SQL > select (6079860-3155764) logging from dual

(6079860-3155764)

-

2924096

SQL > drop table test

The watch has been discarded.

SQL > select * from redo_size

VALUE-

6110356

SQL > create table test nologging as select * from all_objects where 1: 0

The table has been created.

SQL > insert / * + append*/ into test select * from all_objects

25474 rows were created.

SQL > commit

The submission is complete.

SQL > select * from redo_size

VALUE-

6167588

SQL > select (6079860-3155764) logging, (6167588-6110356) nologging from dual

LOGGING NOLOGGING--

2924096 57232'

Based on this result, we can see that:

Noarchivelog, CTAS, redo=noarchivelog, CTAS nologging, redo= archivelog, CTAS nologging, redo.

In all three cases, less redo size is generated to the system.

It is only under archivelog that CTAS produces more redo size.

After comparing CTAS with CTAS+insert append, in fact (archivelog mode):

CTAS nologging redo = CTAS (no data) nologing + insert append redo size

CTAS redo = CTAS (no data) + insert append redo size

This is the end of the test on how to conduct CTAS and insert append. I hope the above content can improve everyone. If you want to learn more knowledge, please pay more attention to the editor's updates. Thank you for following the website!

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.