学习笔记二－nologging对move和rebuild操作的影响

1。测试nologging对move操作影响。

（1）创建2个同样的表test1和test2，分别为logging和nologging

SQL> create table test logging as select * from sys.dba_objects;
 
Table created.
 
SQL> insert into test select * from test;
 
5923 rows created.
 
SQL> /
 
11846 rows created.
 
SQL> /
 
23692 rows created.
 
SQL> /
 
47384 rows created.
 
SQL> /
 
94768 rows created.
 
SQL> /
 
189536 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> rename test to test1;
 
Table renamed.
 
SQL> create table test2 nologging as select * from test1;
 
Table created.
 
SQL> select count(*) from test1;
 
  COUNT(*)
----------
    379072
 
SQL> c/1/2
  1* select count(*) from test2
SQL> /
 
  COUNT(*)
----------
    379072
 
SQL> select table_name,logging from user_tables where table_name like '%TEST%';
 
TABLE_NAME                     LOG
------------------------------ ---
TEST1                          YES
TEST2                          NO
TO_TEST                        YES

（2）分别对test1和test2进行move操作，观察各自生成的redo大小
 
SQL> @mystat "redo size"
 
NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                           274604664
 
SQL> alter table test1 move;
 
Table altered.
 
SQL> @mystat2
 
NAME                                                        V DIFF
-------------------------------------------------- ---------- ----------------
redo size                                           312311416       37,706,752
 
SQL> @mystat "redo size"
 
NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                           312311416
 
SQL> alter table test2 move;
 
Table altered.
 
SQL> @mystat2
 
NAME                                                        V DIFF
-------------------------------------------------- ---------- ----------------
redo size                                           312392156           80,740

可见，对于logging的表，move操作生成37MB日志，而对于nologging的表，只生成了80k日志，后者性能优势明显。

2。测试nologging对rebuild index操作影响。

（1）分别在test1和test2的object_name列上创建索引

SQL> create index idx_test1 on test1(object_name) logging;
 
Index created.
 
SQL>  create index idx_test2 on test2(object_name) nologging;        
 
Index created.
 
SQL> insert into test1 select * from test1;
 
369073 rows created.
 
SQL> insert into test2 select * from test2;
 
369073 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> select sum(bytes)/1024/1024 from user_segments where segment_name='TEST2'
  2  ;
 
SUM(BYTES)/1024/1024
--------------------
                  72
 

SQL> select sum(bytes)/1024/1024 from user_segments where segment_name='TEST1';
 
SUM(BYTES)/1024/1024
--------------------
                  72

（2）分别对idx_test1和idx_test2进行rubuild
 
SQL> @mystat "redo size"
 
NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                           645506444
 
SQL> alter index idx_test1 rebuild;
 
Index altered.
 
SQL> @mystat2
 
NAME                                                        V DIFF
-------------------------------------------------- ---------- ----------------
redo size                                           669364052       23,857,608
 
SQL> @mystat "redo size"
 
NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                           669364052
 
SQL>  alter index idx_test2 rebuild;
 
Index altered.
 
SQL> @mystat2
 
NAME                                                        V DIFF
-------------------------------------------------- ---------- ----------------
redo size                                           669621404          257,352

可见，对于logging的索引，rebuild时生成了23MB日志，而对于nologging的索引，rebuild时只生成了257KB日志。