Вот результаты тестов.
Вывод - direct insert+nologging НЕ пишется в реду никогда
Так что были и мы правы и квадро - каждый по-своему :)
append + nologging+ARCHIVELOG
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> create table obj_test nologging as select * from all_objects where 1=0;
Table created.
SQL> insert /*+ append */ into obj_test select * from all_objects;
46383 rows created.
Statistics
----------------------------------------------------------
2008 recursive calls
195 db block gets
297001 consistent gets
5 physical reads
24428 redo size
834 bytes sent via SQL*Net to client
586 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
15 sorts (memory)
0 sorts (disk)
46383 rows processed
|
append+ARCHIVELOG
инстанс другой, здесь немного больше объектов поэтому where rownum<46384
SQL> drop table obj_test;
Table dropped.
SQL> create table obj_test as select * from all_objects where 1=0;
Table created.
SQL> insert /*+ append */ into obj_test select * from all_objects where rownum<46384;
46383 rows created.
Statistics
----------------------------------------------------------
1728 recursive calls
196 db block gets
289187 consistent gets
5 physical reads
5051424 redo size
834 bytes sent via SQL*Net to client
605 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
15 sorts (memory)
0 sorts (disk)
46383 rows processed
|
append + nologging+NOARCHIVELOG
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL> drop table obj_test;
Table dropped.
SQL> create table obj_test nologging as select * from all_objects where 1=0;
Table created.
SQL> insert /*+ append */ into obj_test select * from all_objects;
46383 rows created.
Statistics
----------------------------------------------------------
1927 recursive calls
195 db block gets
296967 consistent gets
653 physical reads
24428 redo size
835 bytes sent via SQL*Net to client
586 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
15 sorts (memory)
0 sorts (disk)
46383 rows processed
|
append+NOARCHIVELOG
SQL> drop table obj_test;
Table dropped.
SQL> create table obj_test as select * from all_objects where 1=0;
Table created.
SQL> insert /*+ APPEND */ into obj_test select * from all_objects;
46383 rows created.
Statistics
----------------------------------------------------------
1208 recursive calls
192 db block gets
296807 consistent gets
0 physical reads
24428 redo size
837 bytes sent via SQL*Net to client
586 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
46383 rows processed
|
обычный insert
SQL> drop table obj_test;
Table dropped.
SQL> create table obj_test as select * from all_objects where 1=0;
Table created.
SQL> insert into obj_test select * from all_objects;
46383 rows created.
Statistics
----------------------------------------------------------
1197 recursive calls
3586 db block gets
297429 consistent gets
0 physical reads
5011828 redo size
854 bytes sent via SQL*Net to client
572 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
46383 rows processed
|
insert + nologging + noarchivelog
SQL> drop table obj_test;
Table dropped.
SQL> create table obj_test nologging as select * from all_objects where 1=0;
Table created.
SQL> set autotrace on statistics;
SQL> insert into obj_test select * from all_objects;
46383 rows created.
Statistics
----------------------------------------------------------
1230 recursive calls
3587 db block gets
297429 consistent gets
0 physical reads
5019636 redo size
842 bytes sent via SQL*Net to client
572 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
46383 rows processed
|
-----------------------
Вечны налоги,
Смерть и потеря данных.
Что на этот раз?
 |
Re: nologging & /*+ APPEND */ [new]