oracle alter table logging,oracle_操作如何减少产生日志量.nologging

SQL> archive log list

Database log mode  Archive Mode

Automatic archival  Enabled

SQL> CREATE TABLE logtab AS SELECT * FROM dba_objects WHERE

1=2;

Table created.

SQL> SELECT force_logging FROM v$database;

FOR

---

NO

SQL>

SQL> SELECT logging FROM dba_tables WHERE

table_name='LOGTAB';

LOG

---

YES

SQL> INSERT INTO logtab SELECT * FROM dba_objects ;

14524 rows created.

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

1593448  redo size

SQL>  INSERT INTO logtab SELECT * FROM

dba_objects ;

14524 rows created.

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

1582344  redo size

SQL> alter table test.logtab nologging;

Table altered.

SQL> SELECT logging FROM dba_tables WHERE

table_name='LOGTAB';

LOG

---

NO

SQL> SQL>  INSERT INTO logtab SELECT *

FROM dba_objects ;

14524 rows created.

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

1534188  redo size

SQL> INSERT INTO logtab SELECT * FROM dba_objects ;

14524 rows created.

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

620  redo

size

SQL>

SQL> alter database force logging;

Database altered.

SQL>

SQL>

SQL> SELECT force_logging FROM v$database;

FOR

---

YES

SQL> SELECT logging FROM dba_tables WHERE

table_name='LOGTAB';

LOG

---

NO

SQL>  INSERT INTO

logtab SELECT * FROM dba_objects ;

14524 rows created.

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

1541864  redo size

SQL>  INSERT INTO logtab SELECT * FROM

dba_objects ;

14524 rows created.

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

1580076  redo size

SQL> INSERT INTO logtab SELECT * FROM dba_objects

nologging;

14524 rows created.

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

1534084  redo size

SQL> INSERT INTO logtab (object_id,object_name)

VALUES(111111,'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDD');

1 row created.

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

328

redo size

SQL> INSERT  INTO logtab

(object_id,object_name)

VALUES(111111,'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDD');

1 row created.

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

328  redo

size

SQL>  INSERT INTO logtab

(object_id,object_name)

VALUES(111111,'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDD');

1 row created.

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

440  redo

size

归档模式下，只有NOLOGGING+APPEND才能产生少量日志。对于INSERT

VALUES模式APPEND不起作用。

非归档模式下，不管nologging、logging，

append都会减少日志量。

----不正确的NOLOGGING 操作。

INSERT INTO T1 SELECT * FROM T2 NOLOGGING;

INSERT INTO T1 VALUES ('0');

INSERT INTO T1 SELECT * FROM T2;

DELETE FROM T1;

UPDATE T1 SET A='1';

----正确的NOLOGGING 操作。

CREATE TABLE T1 NOLOGGING AS SELECT * FROM T2;

CREATE INDEX T1_IDX ON T1(A) NOLOGGING;

ALTER INDEX T1_IDX REDUILD ONLINE NOLOGGING;

对于HINT的正确使用，, 检查顺序是从左向右，当遇到第一个保留字(v$reserved_words)时宣告后面

HINT无效。

综上所述， 少量日志的时候还是比较少的。那么在这种情况下如何提高呢？

对的，是并行(parallel)

，下次我们实验下parallel的HINT以及正确使用。