加载中…oracle_操作如何减少产生日志量.nologging
(2015-06-08 00:55:55)
标签：
nologgingredooracledml |
分类： oracleops |
----------------------归档模式
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.
----------------------------------------------------------
SQL> INSERT INTO logtab SELECT * FROM
dba_objects ;
14524 rows created.
----------------------------------------------------------
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.
----------------------------------------------------------
SQL> INSERT INTO logtab SELECT * FROM dba_objects ;
14524 rows created.
----------------------------------------------------------
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.
----------------------------------------------------------
SQL> INSERT INTO logtab SELECT * FROM
dba_objects ;
14524 rows created.
----------------------------------------------------------
SQL> INSERT INTO logtab SELECT * FROM dba_objects
nologging;
14524 rows created.
----------------------------------------------------------
SQL> INSERT INTO logtab (object_id,object_name)
VALUES(111111,'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDD');
1 row created.
----------------------------------------------------------
SQL> INSERT INTO logtab
(object_id,object_name)
VALUES(111111,'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDD');
1 row created.
----------------------------------------------------------
SQL> INSERT INTO logtab
(object_id,object_name)
VALUES(111111,'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDD');
1 row created.
----------------------------------------------------------
归档模式下，只有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以及正确使用。
喜欢
0
赠金笔